Adjust Dynamic format of a Measure

[sa34techer]

I am using dynamic format with a SWITCH function to display units a measure.  Please assist me with adjusting my SWITCH function(below), to numbers that end in K should have the decimal after the dollar sign and no decimal point.

• Change $915.1K to $.915K (Example)

var salesMeasure = [.Total Revenue]

return

SWITCH(TRUE(),

AND(SalesMeasure>-1000000000,SalesMeasure<=-1000000),"$#,##0,,.0M",

AND(SalesMeasure>-1000000,SalesMeasure<=-1000),"$#,##0,.0K",

salesMeasure>1000 && salesMeasure<100000,"$#,.0K",

salesMeasure<1000000, "$#,.0K",

salesMeasure<1000000000, "$#,,.0M",

salesMeasure>=1000000000, "$#,,,.0B")

[Bibiano_Geraldo · Accepted solution]

Hi @sa34techer ,

Here's updated DAX, please let me know if satisfies your needs:

var salesMeasure = [Total Revenue]
return
SWITCH(TRUE(),
    AND(salesMeasure > -1000000000, salesMeasure <= -1000000), "$#.0,,M",  -- For millions
    AND(salesMeasure > -1000000, salesMeasure <= -1000), "$#.0,.K",  -- For thousands (negative)
    salesMeasure > 1000 && salesMeasure < 1000000, "$#.0,.K",  -- For thousands (positive)
    salesMeasure < 1000000, "$#.0,.K",  -- For thousands (positive)
    salesMeasure < 1000000000, "$#.0,,M",  -- For millions
    salesMeasure >= 1000000000, "$#.0,,,B"  -- For billions
)

[Bibiano_Geraldo · Accepted solution]

Hi @sa34techer ,

Here's updated DAX, please let me know if satisfies your needs:

var salesMeasure = [Total Revenue]
return
SWITCH(TRUE(),
    AND(salesMeasure > -1000000000, salesMeasure <= -1000000), "$#.0,,M",  -- For millions
    AND(salesMeasure > -1000000, salesMeasure <= -1000), "$#.0,.K",  -- For thousands (negative)
    salesMeasure > 1000 && salesMeasure < 1000000, "$#.0,.K",  -- For thousands (positive)
    salesMeasure < 1000000, "$#.0,.K",  -- For thousands (positive)
    salesMeasure < 1000000000, "$#.0,,M",  -- For millions
    salesMeasure >= 1000000000, "$#.0,,,B"  -- For billions
)

[sa34techer]

You suggestion did not work.   Results below.

[jgeddes · Accepted solution]

Maybe something like this would work for you...

formattedMeasure = 
var _salesMeasure = 
SUM('Table'[Values])
var _result =
SWITCH(
    TRUE(),
    ABS(_salesMeasure) > 1000000000, "$"&FORMAT(_salesMeasure, "#,,,.0B"),
    ABS(_salesMeasure) > 1000000, "$"&FORMAT(_salesMeasure, "#,,.0M"),
    ABS(_salesMeasure) > 1000, "$."&FORMAT(_salesMeasure,"#,K"),
    ABS(_salesMeasure) > 0, "$"&FORMAT(_salesMeasure,"#")
)
RETURN
_result