powerbidaxdaxstudio

group and sum in DAX

I have a sample code as below. I would like to find the currency rate for each currency to multiply by its amount.

Curreny  Amount
usd          100
eur           75
cny           50
eur           25
jpy           25

Expected answer

100*1 + 75*0.9 + 50*0.5 + 25*0.9 + 25*0.7

What i am trying to do is not working is:

  _RateMeasure1 = 
    
    VAR CurrencyAmounts = SUMMARIZE('myDF',myDF[Currency], 'myDF'[Amount])
    
    VAR USD_Rate = MAXX(FILTER('_SlicerTable', _SlicerTable[Currency] = "USD"), '_SlicerTable'[RateCol1])
    VAR EUR_Rate = MAXX(FILTER('_SlicerTable', _SlicerTable[Currency] = "EUR"), '_SlicerTable'[RateCol1])
    VAR CNY_Rate = MAXX(FILTER('_SlicerTable', _SlicerTable[Currency] = "CNY"), '_SlicerTable'[RateCol1])
    VAR JPY_Rate = MAXX(FILTER('_SlicerTable', _SlicerTable[Currency] = "JPY"), '_SlicerTable'[RateCol1])
    
    RETURN
    SUMX(
        CurrencyAmounts,
        SWITCH(
            myDF[Currency],
            "USD", 'myDF'[Amount] * USD_Rate,
            "EUR", 'myDF'[Amount] * EUR_Rate,
            "CNY", 'myDF'[Amount] * CNY_Rate,
            "JPY", 'myDF'[Amount] * JPY_Rate
        )
    )

_SlicerTable is a table as below

Currency   RateCol1   
USD         1          
CNY         0.5        
EUR         0.9        
JPY         0.7
Solution

  • This can be done by the following:

    1. Create a dimension table for your currency conversions

      enter image description here

    2. Create a relationship with your fact table like so connecting Currency - Currency enter image description here

    3. Lastly you can create a measure to calculate your conversion

      _RateMeasure = SUMX(myDF,myDF[Amount]*RELATED('Currency Conversion'[RateCol1])) --sumx is an iterator function and goes row by row. Related references the related column RateCol1

    enter image description here