Forum Discussion

FilipaPereira's avatar
FilipaPereira
Frequent Visitor
1 year ago
Solved

Dynamic formatting

How to apply dynamic formatting to the total.
I've applied dynamic formatting to distinguish between different currencies, € and $, but the total always appears in €, even if I only select data in $. How do I apply dynamic formatting to the total?
Here's what I've done

this is the formula I used

SWITCH(SELECTEDVALUE('Comisões'[Moeda]),
  "USD", "#,### $ "," #,###,## €")
 
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi FilipaPereira  - you can create a measure as like below:

    Currency Used =
    IF (
    HASONEVALUE('Comisões'[Moeda]),
    SELECTEDVALUE('Comisões'[Moeda]),
    IF (
    COUNTROWS(VALUES('Comisões'[Moeda])) = 1,
    VALUES('Comisões'[Moeda]),
    "Mixed"
    )
    )

     

    another measure is for formatting the total detection follow: replace the table name as per your model

    Formatted Total Commissions =
    VAR _value = [TotalCommissions]
    VAR _currency = [Currency Used]

    RETURN
    SWITCH(
    _currency,
    "USD", FORMAT(_value, "#,##0.00 $"),
    "EUR", FORMAT(_value, "#,##0.00 €"),
    "Mixed", FORMAT(_value, "#,##0.00") & " (Mixed)",
    FORMAT(_value, "#,##0.00") // fallback
    )

     

    Hope this helps.

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi FilipaPereira  - you can create a measure as like below:

    Currency Used =
    IF (
    HASONEVALUE('Comisões'[Moeda]),
    SELECTEDVALUE('Comisões'[Moeda]),
    IF (
    COUNTROWS(VALUES('Comisões'[Moeda])) = 1,
    VALUES('Comisões'[Moeda]),
    "Mixed"
    )
    )

     

    another measure is for formatting the total detection follow: replace the table name as per your model

    Formatted Total Commissions =
    VAR _value = [TotalCommissions]
    VAR _currency = [Currency Used]

    RETURN
    SWITCH(
    _currency,
    "USD", FORMAT(_value, "#,##0.00 $"),
    "EUR", FORMAT(_value, "#,##0.00 €"),
    "Mixed", FORMAT(_value, "#,##0.00") & " (Mixed)",
    FORMAT(_value, "#,##0.00") // fallback
    )

     

    Hope this helps.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi FilipaPereira 

     

    At the total level, your formula using SELECTEDVALUE returns blank because there is likely more than one value in 'Comisões'[Moeda]. Since this does not satisfy the first condition in your SWITCH statement, the measure falls back to the alternative result. This usually happens when there is no direct relationship between the slicer column and the column you are referencing in the dynamic format string expression, so the context does not filter down as expected.