Display a formatted value based on a two-value filter

[Zacharie]

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 slicer containing two values : Long Format and Short Format.

If the user chooses Long Format, I want my revenue to be an integer in format currency (i.e. € XX XXX XXX with no commas, only the whole number) 
If the user picks Short Format, I want my revenue to look like this : € 10,5M (for 10 538 359 €), 285,9K (for 285 923)

I can't to this with bookmarks because I already have plenty of bookmarks on this page. 

The general Revenue measure uses a dynamic measure.
In measure, it looks like this : 

Revenue =

    SWITCH (

        SELECTEDVALUE('Currency slicer'[Currency], "EUR" ),

        "EUR", [Revenue EUR],

        "Local", [Revenue LCY],

        [Revenue EUR]

    )

In format, it is like this : 

VAR currency_format =

    SWITCH (

            SELECTEDVALUE('Currency slicer'[Currency]),

            "EUR", "€ #,0",

            "Local", SWITCH(

                SELECTEDVALUE( Sales[currency] ),

                "AED", "AED #,0",

                "AUD", "A$ #,0",

                "BRL", "R$ #,0",

                "SEK", "kr #,0",

                "USD", "$ #,0",

                "#,0"

            ),

            "€ #,0"

        )

RETURN

    SWITCH(

        TRUE(),

        SELECTEDMEASURE() > 1000000000, currency_format & ",,,.0B",

        SELECTEDMEASURE() > 1000000, currency_format & ",,.0M",

        SELECTEDMEASURE() > 1000, currency_format & ",.0K",

        currency_format

    )

I tried to create a new measure called Dynamic Revenue to answer this need but I can't find a solution. 

Do you think I must create two new measures, one for the short format and another one for the long format ? 

Or do you think it is not necessary and I can do it with only one dynamic measure ?

Thank you all in advance !

[Kedar_Pande]

@Zacharie 

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

[PhilipTreacy · Accepted solution]

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