Forum Discussion
Using "Dynamic" format (under Measure Tools) to show different decimal places based on value
I have a single measure that I want to format differently based on rules:
value > 10,000,000 -> $XXX.XM, e.g. $446.1M
value > 1,000,000 -> $X.XXM e.g. $6.81M
value > 100,000 -> $XXX.XK e.g. $182.1K
etc
These values will then be placed on a bar/column chart data label or as a matrix value.
Can I use "Dynamic" under Measure Tools -> Formatting to accomplish this? This is what I have tried and it is not working
var __val = [Budget Pack]
return
SWITCH(TRUE(),
__val > POWER(10,7), "\$###.#M;(\$###.#M);\$##.#M",
__val > POWER(10,6), "\$#.##M;(\$#.##M);\$#.##M",
__val > POWER(10,5), "\$###.#K;(\$###.#K);\$###.#K")I then set "Display Units" under Data label -> Value to "None" and I get $446091912.2.0M (if I set to auto, it seems like dymanic formatting is ignored).
I know I could do potentially do it with FORMAT statements in the measure itself, but the code is already complex enough that I'd like to avoid adding to it if possible.
Thanks,
David
I figured out that I had the wrong format strings. I can't remember where I saw this pattern first, so apologies to whoever turned me onto it. Here is what works
(Units = None, Decimal places = Auto)
var __val = [Budget Pack] return SWITCH(TRUE(), __val > POWER(10,8), "$#,0,,.M;($#,0,,.M);-", //e.g. $567M __val > POWER(10,7), "$#,0,,.0M;($#,0,,.0M);-", //e.g. $56.7M __val > POWER(10,6), "$#,0,,.00M;($#,0,,.00M);-", //e.g. $5.67M __val > POWER(10,5), "$#,0,.K;($#,0,.K);-") //e.g. $567KDavid
5 Replies
For your reference.
Step 0: I use these data below.
Step 1: I add a column.
Value2 = SWITCH(TRUE(),[Value] > POWER(10,7), FORMAT([Value]/1000000,"$###.#M"),[Value] > POWER(10,6), FORMAT([Value]/1000000,"$#.##M"),[Value] > POWER(10,5), FORMAT([Value]/1000,"$###.#K"))Step 2: I make a column chart.
mickey64 - thank you, but my use case is a measure, not a column. Plus I am attempting to use Format: Dynamic under Measure Tools in order to not further complicate the measure code.
For your reference.
Value3 = SWITCH(TRUE(),MAX([Value]) > POWER(10,7), FORMAT(MAX([Value])/1000000,"$###.#M"),MAX([Value]) > POWER(10,6), FORMAT(MAX([Value])/1000000,"$#.##M"),MAX([Value]) > POWER(10,5), FORMAT(MAX([Value])/1000,"$###.#K"))
I figured out that I had the wrong format strings. I can't remember where I saw this pattern first, so apologies to whoever turned me onto it. Here is what works
(Units = None, Decimal places = Auto)
var __val = [Budget Pack] return SWITCH(TRUE(), __val > POWER(10,8), "$#,0,,.M;($#,0,,.M);-", //e.g. $567M __val > POWER(10,7), "$#,0,,.0M;($#,0,,.0M);-", //e.g. $56.7M __val > POWER(10,6), "$#,0,,.00M;($#,0,,.00M);-", //e.g. $5.67M __val > POWER(10,5), "$#,0,.K;($#,0,.K);-") //e.g. $567KDavid
Hi dedelman_clng ,
Thanks for reaching out to the Microsoft fabric community forum. Thank you for the update.
Best Regards,
Community Support Team
