Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
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
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
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
12 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |