Switching currency in Power BI

Posted On 2020-10-01

In one of my recent Power BI projects I stumbled upon the need to switch currencies between SEK and EUR. The company mainly looks at SEK, as their head quarters are in Sweden but it is a global company and many times the number crunchers that dive into the reports are only interested in EUR. Sure I could build 2 separate reports and put myself in the situation where I need to make all the adjustments twice when changes are requested or I could add a slicer to the report with which you can decide if the numbers are in SEK or EUR. There are probably a couple of different ways you can do this, but this is how I made it work! Feel free to copy as you like.

The Dataset

For my sample I have created 2 tables in an Excel file. You can download this if you want at the end of the post.

I have a transaction log on the left where every transaction is in SEK. On the right I have a Currency rate table that show the average rate for 1 EUR in SEK by that month. It stretches from 2010 until 2020, the same as the transaction log.

Prepping the data

I just imported both tables above as they are but I’ve added a third manual table from a blank query in Power BI. This is named “Currency Selector” and has only one column named Currency. In here I have 2 values; SEK and EUR.

Moving over to the Transactions table, I’ve created a new column for the Currency period. This gives me the first day of every month so that it looks like the date column of the currency rate.

Currency period = Transactions[Date].[Year]&"-"&Transactions[Date].[MonthNo]&"-01"

I’ve added a second column with every transactional row in EUR, translated from my currency rate table.

Sum in EUR = Var ExchangeRate = LOOKUPVALUE('Currency'[Average Rate],'Currency'[Date],Transactions[Currency period])ReturnTransactions[Sum] / ExchangeRate

This column looks up the value in the currency rate table based on the period of the transactional row. It then divides the SUM that is in SEK with the rate to EUR. We now have every transaction in both SEK and EUR! What we need now is the ability to change it on the fly in the report. Time to go back to that Currency Selector table that we’ve created and also create a measure!

I’ve made a measure called Cost. This summaries the SEK or the EUR column depending on if you have filtered the Currency Selector table.

Cost = Var SelectedCurrency = CALCULATE(COUNTROWS(CurrencySelector),FILTER(CurrencySelector,CurrencySelector[Currency]="SEK"))
Return
IF(SelectedCurrency= 1,
// Value if True. We use the SEK column SUM(Transactions[Sum]),
// Value if false. We use the EUR column SUM(Transactions[Sum in EUR]))

Now you just have to drag and drop the measure Cost to your visualizations and add a slicer to your report so that you can select either EUR or SEK. I would suggest make the slicer Single Select so that you couldn’t make the mistake of selecting both currencies.

My slicer is in the left upper corner and this is the view based on SEK or EUR selected from it.

That’s all there is to it folks! Feel free to download the sample data if you want to try it out!

Cheers!