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:

 Currency conversion in DAX for Power BI and SSAS

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:

 Currency conversion in DAX for Power BI and SSAS

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:

 Currency conversion in DAX for Power BI and SSAS

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:

 Currency conversion in DAX for Power BI and SSAS

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.

Let’s block ads! (Why?)

Kasper On BI