Currency conversion in DAX for Power BI and SSAS

Another interesting question that came up recently is how to do currency conversion in Power BI. My colleague Jeffrey Wang had a very neat solution that I want to share more broadly.

Imagine you have sales in 3 different currencies happening on different dates:

image

Now when you are doing reporting you want to report them all in a single currency. So the first thing we need is to get a table with conversion rates for each date for each currency:

image

Next we need to model this so in the report we can choose which currency we want to report on and then automatically apply the right conversion rate. The model looks like this:

image

Both the Sales and the Exchange rate table are fact tables so we need to create 3 dimension tables to tie them together. I use calculated tables to create them as the data is already there:

ExchangeDate = Distinct(FactExchangeRate[Date]).

ReportCurrency = Distinct(FactExchangeRate[ReportCurrency])

TransactionCurrency = Distinct(FactExchangeRate[TransactionCurrency])

The ReportCurrency table will be used to create the slicer in the report that allows us to select the currency. The other 2 tables will allow us to get the correct Exchange rate for the each sales transaction using date and currency. This means that for each row in the factsales tables we filter down to 3 rows in the FactExchangerate table based on the date and currency relationship. Now based on the ReportCurrency slicer we filter it down to a single row thus leaving us with the Exchange rate we need.  The problem here of course is that we need to do this on the fly because for each day we might have transactions using multiple currencies so we need to take that into account.

We can do that using DAX, I start with getting the factor to calculate the exchange rate:

TrnExchangeRate = Min(FactExchangeRate[Factor])

This will get the lowest factor value from the fact table.

Now to calculate the sales per transaction I will use SUMX to achieve this:

TotalSales = if(HASONEVALUE(ReportCurrency[ReportCurrency]),
SumX( FactSales, [Sales] *                                                                                                                                        [TrnExchangeRate]))

This DAX expression will summarize (SUMX)  each row in the FactSales table (that is in the current selection) . For each row it will use the value from the sales column times the Exchange rate returned from the TrnExchangeRate measure. The SUMX is key to get the right numbers, it will iterate over the rows in the fact table dynamically and summarize the results into a single number (currency). Again this is key as each row might be a different currency on a different date and the relationships will make sure only a single value will get returned from the FactExchangeRate.

Putting them all together it allows me to build a report like this:

image

As you can see the 100 Euro’s get translated to 117.44 USD where the USD amounts stay the same. So a clever combination of the model and some DAX will get you currency conversion. Also the grand total is showing the one USD sales number.  Unfortunately there is dynamic formatting yet in Power BI to apply the format on the fly as well.

You can download the sample file here.

  • Nguyễn Minh Trí

    very clever, he uses TrnExchangeRate = Min(FactExchangeRate[Factor]) as calc measure to achieve the rate by its filter context and multiply with current sales value by sumx ^_^ really surprise with the way using sumx

  • Pingback: #Excel Super Links #157 | Excel For You()

  • stotskiy mikhael

    good day
    is it the same thing if make relationship between ‘FactSales’ and ‘FactExchangeRate’
    based on [Date]&[TransactionCurrency]
    and measure like
    TotalSales = IF (
    HASONEVALUE ( FactExchangeRate[ReportCurrency] ),
    SUMX ( ‘FactSales’, [Sales] * CALCULATE ( MIN ( ‘FactExchangeRate'[Factor] ) ) )
    )

    ?
    or it will have performance issues?

    thank you!

    • That will give you a direct many to many which isn’t supported, you need to go through intermediate tables. The measure will probably work but I always would recommend splitting them up for debug and cleanliness sake :).

  • Steven Neumersky

    Before calculated tables, I think we did something general such as:

    SUMX(
    CROSSJOIN(Currency, Date)
    CALCULATE(VALUES(ExchRates[Factor]) * Sales[SalesAmt],
    Filter(Values(ExchRates[DateID]),
    ExchRates[DateID] = MAX(ExchRates[DateID])
    )
    )
    )

    I am so glad variables and calculated tables have come along.
    I like the additional twist you put on this while maintaining leaf level calculations.

  • Pingback: Context Transition in SUMX Function « Li Min's Site()

  • Sameer Bhide

    @Kasper.
    How are the filters propagating in the measure TotalSales

  • ST

    Hi!
    I wonder how would one configure the data model if, the factSales table would have a date range where the sales were valid ( lets say 2 fields [dateFROM] and [dateTO] ), and the convertion rate table also with 2 columns of the same type.

    How would you do this?
    Thank you. 🙂

  • Artur Tolstenco

    I would tweak the sumX to iterate on the distinct combination of sales date and transaction currency only rowstime and not per each sales fact row. It will perforn way better – need just to use sumx over summarize function.