Requirement
A common scenario in multinational companies is having to integrate different currency rates across transactional data.
Quite often sales data is stored regardless of the currency, so the currency code related to each sale is also stored in another column.
So, what we need to do is to integrate the whole daily historical exchange rates for every single currency used within the company.
Then a common approach is to convert through the ETL process all the sales data into one single currency usually USD obviously it can vary from one company to another. This is always a good practice to hold only one single currency in the DWH as it makes it much easier to apply all the business calculation rules.
Let’s assume that in our case we convert all the sales into USD.
However, large international companies have offices and firms all over the world and thus need financial dashboards and reports to be in their different offices’ local currencies.
So, one solution can be to create a dynamic currency conversion measure in Power BI so we can display the USD amount into any needed currencies.
Below I outline how we can solve this particular requirement in a few simple steps:
Modeling
The fact CurrencyRate stores the daily historical exchange rates .
The dimension Currency links the currencies to the fact Currency Rates.
The dimension links the fact Currency Rate and the fact Internet Sales with the date attribute.
Note that if you want to hold and handle multiple currencies in your DWH you’ll need to duplicate the dim Currency, one used as a filter (with a direct link to the fact) and another as conversion calculator like in our scenario (with no link to the fact Sales)
DAX Measure
SalesAmountCurrency =
var _USDMeasure =sum(v_FactInternetSales[SalesAmount])
return
if(ISBLANK(_USDMeasure),blank(),
if(HASONEFILTER(v_DimCurrency[CurrencyName]),
-- the conversion calculation starts here
-- the above part is just used to handle null values
-- and slicer with multiple values allowed
var _sumx =sumx(
CROSSJOIN(
values(v_DimCurrency[CurrencyKey]),
values(DimDate[DateKey])
),
CALCULATE(
var _USDMeasurex =sum(v_FactInternetSales[SalesAmount])
return
Divide(_USDMeasurex,VALUES(FactCurrencyRate[AverageRate]))
)
)
return
_sumx,_USDMeasure
)
)
So this measure simply computes the conversion calculation row-by-row and as you will see in the result section the conversion will be calculated on the fly as we switch from one currency to another.
Result
As you can see the sales amount gets automatically converted into the selected currency.
Make things look nicer
We can also use the above measure and concatenate the currency code with Sales Amout to automatically display the code currency with the sales amount
SalesAmountCurrencyCode =
var _USDMeasure =sum(v_FactInternetSales[SalesAmount])
return
if(ISBLANK(_USDMeasure),blank(),
if(HASONEFILTER(v_DimCurrency[CurrencyName]),
var _sumx =sumx(
CROSSJOIN(
values(v_DimCurrency[CurrencyKey]),
values(DimDate[DateKey])
),
CALCULATE(
var _USDMeasurex =sum(v_FactInternetSales[SalesAmount])
return
Divide(_USDMeasurex,VALUES(FactCurrencyRate[AverageRate]))
)
)
return
if(ISBLANK(_sumx),
blank(),
concatenate(CONCATENATE(FIRSTNONBLANK(v_DimCurrency[CurrencyAlternateKey],1), " "),FORMAT( _sumx, "Standard") )
),concatenate("USD ",FORMAT( _USDMeasure, "Standard") )
)
)
Note that the format type of this measure is now text.
Therefore, we can no longer simply reference this measure in another measure that would require advanced calculation such as ratio, YTD, projection and so on.
Also, if your users use Excel or want to export the PBI visual into Excel, it won’t work as the figures are in text format.
The sample data used in this post is from AdventureWorksDWH2014.
