Dynamic Currency Conversion with DAX in Power BI

# Dynamic Currency Conversion with DAX in Power BI

In this post, I will share how to implement a dynamic currency conversion with Dax.

Table of Contents

## 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)

## Measure – Dynamic Currency Conversion with DAX

```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.

## 5 thoughts on “Dynamic Currency Conversion with DAX in Power BI”

1. Swati says:

What if the data is in stored in different Currencies?

2. Hi Swati,
If your default currency is not USD then you can replace the var _USDMeasure I used in my script with your default currency.
As long as you store the different currency rates based on your default currency you do not need to change anything from my script.

However, if you happen to have data with multiple currencies then you will need to convert your data into a common currency first.
You can do that dynamically into the calculation script but my advice would be to do this conversion during the ETL phase.

3. Chris Turnbull says:

Hi Ben,

Great Article, Thanks.

Uou are using the Average Rate, how would the measure look if you used the Daily Rate and iterated over the Sales Table with that Rate?

Thanks

Chris