Forum Discussion

alexyin1053's avatar
alexyin1053
Icon for Helper I rankHelper I
2 years ago
Solved

Dynamic Format Strings For Chinese Culture Measure

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's avatar
    OwenAuger
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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