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 $...
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.

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.