Currency Conversion in Power BI: Enabling Seamless Multicurrency Switching

[DOLEARY85]

Introduction

Financial data integration in Power BI becomes more complex when dealing with global currencies. While handling single-currency data is straightforward, navigating the complexities of multiple currencies requires a different approach. In this blog, we explore a systematic method for seamless multicurrency display, empowering end users to effortlessly toggle between currencies directly from the dashboard.

For this example, imagine a scenario where a European company with global offices recieves transactions in multiple currencies. Their default currency for reporting is the Euro (EUR) but every office needs to view the report in their local currency.

The first crucial step in achieving our goal is establishing EUR as the primary currency.

Creating a Single Currency

Let’s start with our inputs, first the ‘Financial Data’ table. This example contains 6 records each record has an amount and the currency code related to that record.

We’ll need to source the conversion rate details from primary currency (EUR) to other currencies. For this example I only need EUR to GBP and EUR to USD.

We might end up with a currency table that looks like the below. As EUR is our primary currency and will not need any conversion, it has been given a value of 1.

Now we have our conversion rates, we next need to create a field in our financial data that converts the 'Amount' column to a single currency. For this we’ll leverage Power Query.

The process involves merging the 'Currency Table' with the 'Financial Data' through an inner join, linking on the 'Currency Code' columns.

By expanding the ‘Currency Table’ and integrating the ‘Conversion Rate’ field we can add a custom column that transforms the 'Amount' column into 'Amount in EUR’.

We finish off by transforming the field type to numeric and round the value to 2 decimal places using the ‘Round’ function.

We now have our ‘Amount’ column all in one currency (‘Amount in EUR’).

Creating a Dynamic Dashboard Element

To create a dynamic currency selector that allows the end user to seamlessly switch between currencies, we move to the front end of our dashboard.

First, we introduce a slicer, linked to the ‘Currency Code’ column from the ‘Currency Table’, this will facilitate the currency switching.

Now we can create measures that will use the selection from this slicer and the ‘Amount in EUR’ column we created in Power Query.

To demonstrate this we will create a simple measure, calculating the total amount of all records:

Total Amount Selected Currency = 
CALCULATE(
    SUMX(
        VALUES('Currency Table'[Currency Code]),
        SUM('Finance Data'[Amount in EUR]) * VALUES('Currency Table'[Conversion Rate])
    )
)

Let’s examine this measure in more detail:

VALUES('Currency Table'[Currency Code])

This function returns a single-column table that contains a unique list of 'Currency Code' values from the 'Currency Table'. It essentially provides a list of distinct currencies.

SUM('Finance Data'[Amount in EUR])

This part calculates the total sum of the 'Amount in EUR' column in the 'Finance Data' table.

SUMX(…,SUM('Finance Data'[Amount in EUR]) * VALUES('Currency Table'[Conversion Rate])))

The last part of our measure multiplies the total sum of 'Amount in EUR' by the corresponding 'Conversion Rate' for the selcted currency.

The final step is to incorporate the measure into a card on the dashboard and test it using our slicer.

Conclusion

The ability to seamlessly present financial data in multiple currencies is extremely important for global enterprises. From risk management strategies to compliance with government regulations and market research initiatives. By implementing these structured steps, users can effortlessly navigate and analyse financial data across various currencies for a variety ofmfinancial needs.

Incorporate these techniques into your Power BI toolkit to unlock the full potential of multicurrency financial analysis.

[permits99]

A to Z Permits is a permit expediting firm located in Washington DC.
Our permit expediters streamline the permitting & licensing process
for individuals and business clients to obtain the

[Martin_D]

If you have a real life scenario with different conversion rates at different dates, SQLBI DAX Patterns has extended solutions: https://www.daxpatterns.com/currency-conversion/