Forum Discussion

iBusinessBI's avatar
iBusinessBI
Icon for Kudo Collector rankKudo Collector
1 year ago

Dynamic Format of a measure displays wrong result when using Analyze in Excel

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 in the Service - 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

Need Help

3 Replies

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    1 year ago

    Hi iBusinessBI 

    The issue arises because Analyze in Excel applies the regional settings of the client machine or Excel itself, which overrides custom formatting from Power BI. Unfortunately, Analyze in Excel does not fully support custom number formats as defined in DAX. However, you can work around this limitation by using text-based formatting to preserve your symbols.

    Solution: Force Symbol as Text

    Instead of using dynamic formatting for numbers, convert the values to text with the desired symbol:

     

    Formatted Amount =
VAR c = SELECTEDVALUE('Show Currency'[Currency for Calc])
VAR amount = [YourMeasure]  -- Replace with your actual measure
RETURN
IF(c = "USD",
    "$ " & FORMAT(amount, "#,0"),
    "₪ " & FORMAT(amount, "#,0")
)

     

    Key Changes:

    1. Text Output: The measure outputs a string (text), preserving the $ or ₪ symbol.
    2. Static Format: This avoids regional setting overrides since Excel treats it as text, not a number.

    Caveats:

    • You lose the ability to use numeric operations in Excel (e.g., summing or sorting by the column directly). If numeric operations are needed, keep the original numeric column alongside this formatted version.
    • Adjust your report visuals to hide the numeric field and show the formatted field.

    Let me know if you need further clarification! 😊

     

    Did I answer your question? Mark my post as a solution, this will help others!

    If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

    Kind Regards,
    Poojara
    Data Analyst | MSBI Developer | Power BI Consultant
    YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

    • iBusinessBI's avatar
      iBusinessBI
      Icon for Kudo Collector rankKudo Collector
      1 year ago

      Thanks, Poojara_D12 

      But I DO need the measure to be a NUMBER, not text.

      That's the main reason we use Analyze in Excel - to be able to add more formulae in Excel itself.
      So a Kudo from me, but it does not solve my challenge

      Thanks again

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, iBusinessBI 

    Thanks for Poojara_D12's reply. Trying to replicate your situation, but PBI doesn't seem to support dynamically formatting currency symbols and retaining them in numeric format, how did you achieve this effect in PBI desktop, can you provide pbix/tables with no sensitive data for test.

    Best Regards,
    Yang
    Community Support Team