Forum Discussion
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...
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
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"))