In the world of data visualization and analysis, Power BI has emerged as the top tool for presenting information in a visually appealing and interactive manner. When creating reports and dashboards, formatting data properly is crucial especially when dealing with currency values.

Power BI allows users to manipulate and transform data to meet their specific needs. One of its valuable features is the ability to modify currency formats, ensuring an accurate and consistent display of monetary values.

By adjusting the currency format in Power BI, you can enhance the clarity and comprehension of financial information. This ultimately improves the overall quality of analysis and reporting.

Discover how you can effortlessly customize your reports and dashboards to accurately display monetary values across different currencies such as dollars, euros, pounds, and many more in this article.

Change Currency Format with Measure Tools

There are two methods to modify the currency format in Power BI, and one of them is by utilizing the Measure Tools. Unlike the default tabs displayed upon opening Power BI, the Measure Tools tab becomes active only when you create or edit a measure.

When you create a measure, you’ll find yourself in the Measures tools tab right away. In this tab, within the formatting section, you can conveniently apply your desired currency format simply by clicking on the currency symbol.

Power BI’s default currency is the dollar sign. However, you can change this to any currency you want.

To do this, just click on the downward arrow next to the dollar sign. From there, you can choose the currency symbol that suits your needs.

Change Currency Format with the FORMAT Function

DAX’s FORMAT function provides another option by which you can modify the currency format in Power BI.

The FORMAT function simply allows you to apply any number format you want, including currencies. You can see it as the DAX equivalent of using the Measure tools tab.

In many cases, you won’t need to use the FORMAT function to change currency symbols. Nonetheless, using the FORMAT function comes in handy when creating custom solutions that allow you to change currency symbols based on the applied filter.

One such situation will be addressed in this section.

The FILTER function’s syntax uses three parameters:

FILTER ( <Value>, <Format>, [<LocaleName>] )

• Value – refers to the number or measure that returns a scalar value you want to format.
• Format – the format type you want to apply to the Value.
• LocaleName – refers to the locale name that will determine the formatting style. This is an optional parameter and will use the default locale setting in Power Bi when omitted.

The FORMAT function changes the data type of numeric values to text. Therefore, you won’t be able to use it in visuals where the values section requires numeric values.

Profit (FORMAT) = 
    FORMAT(
        [Profit],
        "£#,###.00"
    )

You can use the FORMAT function this way to change the currency symbol of a measure. The syntax uses familiar formatting symbols from Excel.

Creating a Dynamic Currency Measure

Let’s say you have a report that needs to display an index’s – sales for example – values in various currencies. You can create a measure for applying the correct currency format, and based on your needs, perform currency conversion as well.

This ensures your report is accurate, professional, and meets your specific requirements.

For the purpose of this illustration, a model consisting of four tables – Calendar, Sales by Store, Product Lookup, and Country Currency – is utilized. It is worth noting that the Country Currency table intentionally remains unconnected to any of the other tables.

The table holds vital information regarding a country’s currency and exchange rate, instrumental in achieving the desired formatting goals. It does not have any direct association with the remaining tables in the model.

Total Revenue = 
    SUMX(
        'Sales by Store',
        'Sales by Store'[unit_price] * 'Sales by Store'[quantity_sold]
    )

After setting up the model, a Total Revenue measure is created.

When the measure is added to a table and filtered by Product Category, the result is as follows. You can see the Total Revenue measure is denoted as calculated in dollars.

To obtain the equivalent of the Total Revenue measure in a different currency, it must be converted to the respective country’s value based on the exchange rate. Additionally, it should be displayed using the currency sign of that country.

Achieving this task solely with the Measure tools tab would prove challenging. However, given that all the necessary information resides within the Country Currency table, we can rely on DAX to seamlessly deliver the precise desired outcome.

Country Revenue = 
    VAR Revenue = [Total Revenue] * SELECTEDVALUE ( 'Country Currency'[Exchange Rate] )
    VAR Dollar = FORMAT ( 
            [Total Revenue],
            "$#,###.00"
        )
    VAR SelectedCountry = IF (
            ISBLANK (
                SELECTEDVALUE (
                    'Country Currency'[Country]
                )
            ), 
            Dollar,
            FORMAT (
                Revenue, 
                SELECTEDVALUE (
                    'Country Currency'[Currency Sign]
                ) & 
                "#,###.00"
            )
        )
    RETURN SelectedCountry

This syntax uses a combination of the FORMAT AND SELECTEDVALUE functions to create a dynamic currency format based on the selected country. It also ensures that the revenue is displayed appropriately with the correct currency symbol.

Before breaking the code into small chunks for understanding, let’s look at how the SELECTEDVALUE function works.

SELECTEDVALUE function’s syntax uses two parameters:

SELECTEDVALUE ( <ColumnName>, [<AlternateResult>] )

<ColumnName> – refers to the column that contains the single value.

<AlternateResult> – refers to the value that should be returned when the <ColumnName> column has no value or has multiple values. The <AlternateResult> parameter is optional, and SELECTEDVALUE will return blank when nothing is specified.

The SELECTEDVALUE function returns the value of the column after it has been filtered down to a single value.

Now, here’s a breakdown of the code:

1. The formula begins by defining a variable named Revenue. It calculates the total revenue by multiplying the [Total Revenue] measure with the exchange rate from the Country Currency table for the selected country.
2. The next variable, Dollar, uses the FORMAT function to format the [Total Revenue] measure as a dollar amount with two decimal places and a thousand separator.
3. The SelectedCountry variable is defined using an IF statement. The IF statement checks if a specific country is selected in the Country Currency table. If no country is selected ISBLANK, it assigns the Dollar variable value to SelectedCountry.

Otherwise, it formats the Revenue variable value with the currency sign obtained from the Country Currency table using the formatting rules.

4. Finally, the formula returns the value of SelectedCountry, which will be the formatted revenue for the selected country.

To see how this works, first, a slicer visual is created using the Country column from the Country Currency table.

Afterwards, the Country Revenue measure is added to the table visual.

Now, when the Slicer selects the United Kingdom, the Country Revenue column displays revenue denoted with the pound sign, while simultaneously converting the revenue from dollars to pounds using the exchange rate value in the Exchange Rate column of the Country Currency table.

The same goes for any other country selected in the slicer. When no country is selected, the Country Revenue column simply returns the same value as the Total Revenue column, courtesy of the IF function in the Country Revenue measure.

With this, you would have created a dynamic way to change the currency symbol for any measure.

Country = SELECTEDVALUE('Country Currency'[Country], "United States")

If you want, you can create a measure that displays the selected country using this syntax.

This syntax sets the value of the variable Country to the selected country from the Country Currency table. If no country is selected, it defaults to the United States.

Exchange Rate = “The exchange rate for ” &

    SELECTEDVALUE ( ‘Country Currency'[Country] ) &

    ” is ” &

    SELECTEDVALUE ( ‘Country Currency'[Currency Sign] ) &

    SELECTEDVALUE ( ‘Country Currency'[Exchange Rate] ) &

    “/$”

You can also create another visual that displays the selected country’s exchange rate using this syntax.

The resulting output of this formula will be a dynamic sentence that states the exchange rate for the selected country’s currency in relation to the US dollar.

For example, when the selected country is Canada with a currency sign of $ and an exchange rate of 1.39, the output would be “The exchange rate for United Kingdom is £0.75/$“.

When you add both measures to individual card visuals, they can help improve the table’s comprehension by enhancing clarity and understanding.

Conclusion

The flexibility and versatility of Power BI enable you to showcase financial information in the most meaningful and engaging way possible.

By harnessing the currency formatting capabilities of Power BI using the techniques provided in this post, you can effortlessly represent monetary values in various currencies.

With the ability to accurately display financial information in the desired format, you can enhance the clarity, understanding, and engagement of your data visualizations.