Forum Discussion
Solved
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...
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
Create the Dynamic Revenue Measure
Dynamic Revenue =
VAR SelectedFormat = SELECTEDVALUE('Format Slicer'[Format], "Long Format") // Assuming your slicer table is named 'Format Slicer'
VAR RevenueValue =
SWITCH (
SELECTEDVALUE('Currency slicer'[Currency], "EUR"),
"EUR", [Revenue EUR],
"Local", [Revenue LCY],
[Revenue EUR]
)
RETURN
SWITCH(
SelectedFormat,
"Long Format",
FORMAT(RevenueValue, "€ #,0"), // Long Format as Integer
"Short Format",
SWITCH(
TRUE(),
RevenueValue > 1000000000, FORMAT(RevenueValue / 1000000000, "€ #,0") & "B", // Billion
RevenueValue > 1000000, FORMAT(RevenueValue / 1000000, "€ #,0") & "M", // Million
RevenueValue > 1000, FORMAT(RevenueValue / 1000, "€ #,0") & "K", // Thousand
FORMAT(RevenueValue, "€ #,0") // Less than thousand, no abbreviation
)
)
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn