Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |