Forum Discussion

Zacharie's avatar
Zacharie
Frequent Visitor
1 year ago
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 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 !



General Comment
Need Help
Tutorial Requests
  • PhilipTreacy's avatar
    PhilipTreacy
    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

     

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    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

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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