Forum Discussion
Post ProdigyDisplay units abbreviations based on dollar value
Hi,
I am trying to change number format in matrix table based on dollar value.
I have a measure that calculates sales, [NS]
I want data to be formatted
if([NS]<100,000 return in $0,"K" Ex: $100K
if([NS]>100,000 return in $0.0,,"M" Ex: $0.7M
Can this be done with dax? Appreciate any help.
Hi dokat ,
I apologise for the wrong syntax.
Try this:Test LY = IF ( [NS] > 100000, FORMAT ( DIVIDE ( [NS], 1000000 ), "Currency" ) & "M", FORMAT ( DIVIDE ( [NS], 1000 ), "Currency" ) & "K" )
This is my test result:Does this work now? 🙂
/Tom
4 Replies
- tackytechtom
Most Valuable Professional
Hi dokat ,
Maybe something like this?IF ( [NS] > 100000 , FORMAT ( [NS] / 1000000 , decimals & "M" ) , IF ( [NS] < 100000 , FORMAT ( [NS] / 1000 , decimals & "K" ) ,
FORMAT ( [NS], decimals ) ) )/Tom
dokattackytechtom thanks for your response. I tried the formula but receieved the below error message. Please advise
"Too few arguments were passed to the FORMAT function. The minimum argument count for the function is 2."
- tackytechtom
Hi dokat ,
I apologise for the wrong syntax.
Try this:Test LY = IF ( [NS] > 100000, FORMAT ( DIVIDE ( [NS], 1000000 ), "Currency" ) & "M", FORMAT ( DIVIDE ( [NS], 1000 ), "Currency" ) & "K" )
This is my test result:Does this work now? 🙂
/Tom
- dokat
tackytechtom Yes it does. Thank you. I sthere anyway to get rid of the 2 decimals when it is <$100,000 and only have one decimal if it is >$500,000? also when i use the formula it removes the sorting on the column formats numbers as text string. Is there a work around this where i can still sort?
Ex; $100K or $0.5M.
Thanks