Dynamic Format Strings For Chinese Culture Measure

[alexyin1053]

Dear all,

I'm currently facing an issue of creating dynamic format strings for Chinese audiences. 
We often use a symbol to represent 10K instead of only using thousand and million.

Currently I'm trying to use the dynamic format to fufill my requirement, if the number is lower than 1 million(let's say 51,000), I would prefer the number to show 5.1 (10K)，The following code is what I've tried in FORMAT:

SWITCH(

        TRUE(),
        SELECTEDMEASURE() < 1000000, FORMAT(SELECTEDMEASURE() / 10000, "#,0.0 (10k)",
        "$#,##0,,.0 M"
)

The outcome returns 51 (10k) Instead of 5.1 (10K), which part am I doing wrong?
Any help is appreciated, 

Thanks!

[OwenAuger · Accepted solution]

Hi @alexyin1053 

Since the (10k) format is produced by constructing the formatted value itself, it must be enclosed in double-quotes.

Try this:

VAR DoubleQuoteChar = """"
RETURN
    SWITCH (
        TRUE ( ),
        SELECTEDMEASURE ( ) < 1000000,
            DoubleQuoteChar & FORMAT ( SELECTEDMEASURE ( ) / 10000, "#,0.0" ) & " (10k)" & DoubleQuoteChar,
        "$#,##0,,.0 M"
    )

Regards

[Anonymous · Accepted solution]

Hi, @alexyin1053 

You can try @OwenAuger  method, if it does not work, you can refer to the following way.

SWITCH(
    TRUE(),
    SELECTEDMEASURE() < 1000000, FORMAT(ROUND(SELECTEDMEASURE() / 10000, 1), "#,0.0 (10k)"),
    "$#,##0,,.0 M"
)

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

[OwenAuger · Accepted solution]

Hi @alexyin1053 

Since the (10k) format is produced by constructing the formatted value itself, it must be enclosed in double-quotes.

Try this:

VAR DoubleQuoteChar = """"
RETURN
    SWITCH (
        TRUE ( ),
        SELECTEDMEASURE ( ) < 1000000,
            DoubleQuoteChar & FORMAT ( SELECTEDMEASURE ( ) / 10000, "#,0.0" ) & " (10k)" & DoubleQuoteChar,
        "$#,##0,,.0 M"
    )

Regards

[alexyin1053]

Hi @OwenAuger ,

Huge Thanks! This did solved my question!