Dynamic formatting not showing the correct currency in Excel

[treatas]

Hi team,

I am using the dynamic formatting for a switch measure to ensure to display the data in correct format according to the selected measure.

It works all fine until I visualise the data in Excel. I get £ sign instead of $ sign.

Here is the dynamic formatting code: 

SWITCH(

    TRUE(),

    [SelectedMeasure] = 1, "$#,##0", // Revenue

    [SelectedMeasure] = 2, "#,##0", // Units

    [SelectedMeasure] = 3, "#,##0", // Accounts

    [SelectedMeasure] = 4, "$#,##0", // ADS Revenue

    [SelectedMeasure] = 5, "#,##0", // ADS Units

    [SelectedMeasure] = 6, "$#,##0", // Revenue On Pace

    [SelectedMeasure] = 7, "#,##0", // Units On Pace

    [SelectedMeasure] = 8, "$#,##0", // ASP

    [SelectedMeasure] = 9, "#,##0", // Units / Account

    [SelectedMeasure] = 11, "0%", // IVL/PCI%

    [SelectedMeasure] = 12, "0%", // IVL/ROTA%

    [SelectedMeasure] = 13, "$#,##0", // Revenue (actual fx)

    [SelectedMeasure] = 14, "#,##0", // Units Coronary

    [SelectedMeasure] = 15, "#,##0", // Units Peripheral

    [SelectedMeasure] = 16, "#,##0", // Orders

    [SelectedMeasure] = 18, "$#,##0", // Revenue Coronary

    [SelectedMeasure] = 19, "$#,##0", // Revenue Peripheral

    [SelectedMeasure] = 20, "#,##0", // Units FOC

    [SelectedMeasure] = 21, "0%", // Units FOC %

    [SelectedMeasure] = 22, "0%", // C2/PCI%

    [SelectedMeasure] = 23, "0%", // C2/ROTA%

    "General" // Default format

)

I tried adding FORMAT([SelectedMeasure]) but it returns wrong results.

Kindly advise,

Best regards,

Hugo

[iBusinessBI]

Have you found a solution?

I have a column that allows to switch between Currencies: USD $ or NIS ש"ח

In the Dynamic format I have a condition that returns the relevant symbol before the number shown:

VAR c = SELECTEDVALUE('Show Currency'[Currency for Calc])

RETURN

IF(c="USD","$ #,0","₪ #,0")

All works great in the Power BI but when I use Analyze in Excel - it changes the dollar sign $ to my ש"ח sign because of the Regional Settings.

I don't want to change the Regional Settings, I want to make it show exactly the symbols that I have written in the Dynamic Format.

I tried using UNICHAR:

IF(c="USD",UNICHAR(36)&" #,0",UNICHAR(8362)&" #,0")

But still the same issue.

Please help

[Anonymous · Accepted solution]

Hi, @treatas 

Based on the instructions you provided, the dynamic formatting settings in Power BI appear to have been correct, but there is a problem when exporting or visualizing this data in Excel, where the currency symbol defaults to £ instead of $. This issue may be related to regional settings in Excel that may supersede the formatting settings specified in Power BI.Excel uses the system's regional settings to calculate currency symbols, and if the system is set to a region that defaults to the currency symbol £, then Excel displays £ instead of $.

There are several steps you can take to troubleshoot and possibly resolve this problem:

1. Check Excel's region settings to ensure that Excel's region settings match the desired currency format. You can adjust this by going to Excel Options > Advanced > Edit Options and selecting the correct currency symbol.

2. Define the currency symbol explicitly in Power BI, trying to use Unicode characters as the dollar sign (U+0024) or a specific currency symbol function in DAX (if available) instead of using "$" in the dynamic formatting code. This may force the correct symbols regardless of regional settings.

3. Review the export settings and make sure the export settings do not change the formatting when exporting data from Power BI to Excel.

Related link: Export data from a Power BI visualization - Power BI | Microsoft Learn

4. Adjust the Excel template, if you are using an Excel template to visualize the exported data, make sure that the template itself has applied the correct currency settings to the cells that are expected to display the currency values.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum