Using "Dynamic" format (under Measure Tools) to show different decimal places based on value

[dedelman_clng]

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

[dedelman_clng · Accepted solution]

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. $567K

David

[v-menakakota]

Hi @dedelman_clng ,
Thanks for reaching out to the Microsoft fabric community forum. Thank you for the update.

Best Regards, 
Community Support Team

[mickey64]

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.

[dedelman_clng]

@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.

[mickey64]

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"))