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.

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.

12 thoughts on “Dynamic Currency Conversion with DAX in Power BI

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

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

  3. Hi Ben,

    Im trying to apply this on the same dataset (adworks2016) but it seems too only return values for currencys that there are sales on in InternetSales (for example USD or AUD). In other words I just get blank values on Sales for conversion to Yen or Bolivar for exemple. Do you know what might be wrong?

    1. Hi Anton,

      What comes to my mind is that either you have a direct relationship between the Currency table and the Sales table thus when you select a currency it will propagate the filter to the Sales table.
      Or if you have reproduced the exact same model of this post I’d say that the fact CurrencyRate is only holding currency for USD and AUD.

      I get 14 distinct currencies in ADWorks2014/ADWorks2016 when I run the following query.
      SELECT distinct [CurrencyKey]
      FROM [AdventureWorksDW2016].[dbo].[FactCurrencyRate]

      Hope this helps.

  4. Hi,
    I have created the currency table, I want to use currency rate with my sales in COLUMN which not a MEASURE.
    how can I do this?

    1. Hi Geeths,
      The main purpose to have the currency conversion in a measure is to apply currency conversion dynamically on any visuals, if you want to do the calculation in a column it may a better idea to achieve it in the ETL process this is usually a better approach than using dax calculated column.
      In your scenario, I would create a measure based on your sales column such as “sum(sales column)” and use this measure with the same logic.

  5. when i am rounding the values to zero decimal place i am getting difference in grand totals

    1. if i selected country india , i want to display my total cost & revenue.is in INR , and if i selected the country USA its need to display in dollar, how i will write the formula.in powerbi

      1. Hi Sharon,
        This is what the second measure in this post is doing so it should work in your scenario asusming that you have the same model as me a dim date a dim currency and fact with the exchange rate.
        By the way this is an old post that I did not take time to update since the calculation group came out so I’d advise you to read this post which is more up to date and laverage calculation group: https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/

Leave a Reply

Your email address will not be published. Required fields are marked *