Forum Discussion
Zacharie
1 year agoFrequent Visitor
Display a formatted value based on a two-value filter
Hello everyone, I have a report containing a table with basic sales data, especially revenue. I want the users of this report to be able to choose the way the values are displayed. I created a s...
- 1 year ago
Hi Zacharie
Download PBIX file with example below
I think this single measure will do it for you. NOTE that I've create a dummy measure [Revenue_] that doesn't do any conversion betwen currencies but works for the sake of this example. You'll need to insert your own measure into the code below
Revenue Formatted = VAR _rev = FORMAT([Revenue_],"### ### ### ###") VAR _magnitude = ROUNDDOWN ( DIVIDE ( LOG10 ( [Revenue_] ), 3 ), 0 ) VAR _format = SELECTEDVALUE('Format'[Format]) VAR _currency = SWITCH( SELECTEDVALUE('Currency'[Currency]), "EUR", "€ ", "AED", "AED ", "AUD", "A$ ", "BRL", "R$ ", "SEK", "kr ", "USD", "$ ", "€ " ) RETURN IF(_format = "Long", _currency & _rev , //ELSE Format = "Short" SWITCH( _magnitude, 0, _currency & FORMAT([Revenue_],"#,0"), -- Integer number 1, _currency & FORMAT([Revenue_],"#,0,.0#K"), -- Thousand 2, _currency & FORMAT([Revenue_],"#,0,,.0#M"), -- Million 3, _currency & FORMAT([Revenue_],"#,0,,,.0#B"), -- Billion _currency & FORMAT([Revenue_],"#,0,,,,.0#T") -- Trillion ) )Check out this guide on formatting FORMAT – DAX Guide
Regards
Phil
PhilipTreacy
1 year agoSuper User
Hi Zacharie
Download PBIX file with example below
I think this single measure will do it for you. NOTE that I've create a dummy measure [Revenue_] that doesn't do any conversion betwen currencies but works for the sake of this example. You'll need to insert your own measure into the code below
Revenue Formatted =
VAR _rev = FORMAT([Revenue_],"### ### ### ###")
VAR _magnitude = ROUNDDOWN ( DIVIDE ( LOG10 ( [Revenue_] ), 3 ), 0 )
VAR _format = SELECTEDVALUE('Format'[Format])
VAR _currency =
SWITCH(
SELECTEDVALUE('Currency'[Currency]),
"EUR", "€ ",
"AED", "AED ",
"AUD", "A$ ",
"BRL", "R$ ",
"SEK", "kr ",
"USD", "$ ",
"€ "
)
RETURN
IF(_format = "Long", _currency & _rev
, //ELSE Format = "Short"
SWITCH(
_magnitude,
0, _currency & FORMAT([Revenue_],"#,0"), -- Integer number
1, _currency & FORMAT([Revenue_],"#,0,.0#K"), -- Thousand
2, _currency & FORMAT([Revenue_],"#,0,,.0#M"), -- Million
3, _currency & FORMAT([Revenue_],"#,0,,,.0#B"), -- Billion
_currency & FORMAT([Revenue_],"#,0,,,,.0#T") -- Trillion
)
)
Check out this guide on formatting FORMAT – DAX Guide
Regards
Phil