Forum Discussion

dedelman_clng's avatar
dedelman_clng
Community Champion
3 months ago
Solved

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. $18...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    3 months ago

    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

dedelman_clng's avatar
dedelman_clng
Community Champion
3 months ago

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's avatar
v-menakakota
Community Support
3 months ago

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

Best Regards, 
Community Support Team