Dynamic currency for different country

[vinay_naran]

Hi all,

I have blelow table with Curr_format column and want to show Sale with proper currancy fromat using Curr_Format column. 

Country	Sale	Curr_format
India	35567620	Rs ##\,##\,##\,#00.0
United Kingdom	467754	£ #,00.0
United State	97070686	$ #,00.0
Japan	456785	¥ #,00.0
Rusia	445656	₽ #,00.0
Pakistan	555665	Rs ##\,##\,##\,#00.0
Portugal	345457	€ #,00.0
Swiss	56644	₣ #,00.0

I have tried to but not working for Pakistan (You can see one Extra comma (,) appears.

Sale Measure = Sum(Sale)

Is there any way I can do?

Thanks

Vinay

[v-heq-msft · Accepted solution]

Hi @vinay_naran ,
As stated earlier, commas are used to separate by three digits. So the separation is done only when the corresponding condition is full. However, when using \ for forced transposition, it is not displayed when there is no such digit. Therefore it is not possible to use one expression to determine all the cases. Therefore, you can use the following equation to match dynamically without changing the original data

IF(
    SELECTEDVALUE('Table'[Country]) = "India" || SELECTEDVALUE('Table'[Country]) = "Pakistan",
    IF(
        SELECTEDVALUE('Table'[Sale]) / 10000000 >= 1,"RS ##\,##\,##\,#00.0",
        IF(
            SELECTEDVALUE('Table'[Sale]) / 100000 >= 1,"RS##\,##\,#00.0",
            "##\,#00.0"
        )
    ),
    SELECTEDVALUE('Table'[Curr_format])
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[vinay_naran]

Thanks you Albert, It works. 

[v-heq-msft]

Hi @vinay_naran ,

According to your description, your custom formatting appears to have extra commas. The first thing that needs to be clarified is that in power bi, ( # ) number placeholders. Displays a number or nothing at all. If the expression has a number at the position where # appears in the format string, that number is displayed; otherwise, nothing is displayed at that position. This symbol acts like a zero placeholder. However, if the number of digits is equal to or less than the number of # characters on either side of the decimal separator in the format expression, leading or trailing zeros are not displayed. And the ( , ) thousands separator. The period is used as the thousands separator in some regional settings. The thousands separator is used to separate thousands from hundreds in numbers with four or more digits to the left of the decimal separator. If the format includes a thousands separator surrounded by numeric placeholders (0 or #), standard usage of the thousands separator is specified. Two adjacent thousand separators, or a thousand separator immediately to the left of a decimal separator (whether or not a decimal is specified), means “reduce the number by dividing the number by 1000, rounding as necessary”.
So for your expression, data less than a certain order of magnitude range will show a null before the comma. For the data you provided, you could change the string after Pakistan to

Rs ##\,##\,#00.0

Final output

Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn
Use custom format strings in Power BI Desktop - Power BI | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[vinay_naran]

Hi Albert,

Thanks for explanation and help. Yes I can use the Curr_format what  you suggested and it working but you can see there are two different curr_format for Pakistan and India, that I don't want. Is there any way I can use one curr_format for both country regardless how big is the number(Sale). 

FYI: India and Pakistan use same number system and thousand separator.

Appreciate you help.

Thanks

Vinay

[Greg_Deckler]

@vinay_naran What should Pakistan look like?