Conditional formatting based on multiple conditions in Pivoted Data

[Anonymous]

Hi ,

Currently working on a conditional formatting logic , but stuck with the same to have the conditional formatting on Profit Colum with multiple conditions in a Pivoted Data

Requirement :- 

Apply conditional Formatting on Profit% based on the Actual and Target

i.e Conditional Formatting =
Var a = SELECTEDVALUE('Table'[Attribute])
VAR B= SELECTEDVALUE('Table'[Value])
VAR C= CALCULATE(SUM'Table'[Value]),FILTER('Table','Table'[Attribute]="Actual"))
VAR D=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Attribute]="Target"))
RETURN SWITCH(TRUE(),
a="Profit%" && C=0 ,"Red",
a="Profit%" && D=0 ,"Orange")

** The data will be in Pivoted form and need to build logic on the same

Sample Data

Area	Geography	Attribute	Values
Germany	Europe	Actual	100
Germany	Europe	Target	0
Germany	Europe	Profit%	40
Australia	Oceania	Actual	0
Australia	Oceania	Target	99
Australia	Oceania	Profit%	23

Regards,

[Anonymous · Accepted solution]

Hi @Anonymous 

I changed to your measure to the following, then it can work, you can refer it.

Measure =
VAR a =
    SELECTEDVALUE ( 'Table'[Attribute] )
VAR B =
    SELECTEDVALUE ( 'Table'[Value] )
VAR C =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Attribute] = "Actual"
                && [Area] IN VALUES ( 'Table'[Area] )
        )
    )
VAR D =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Attribute] = "Target"
                && [Area] IN VALUES ( 'Table'[Area] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        a = "Profit%"
            && C = 0, "Red",
        a = "Profit%"
            && D = 0, "Orange"
    )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Deepak_22  still the logic doesnot work , have attached sc for reference , as for both the conditions , the background is red instead red & Orange

[Deepak_22]

Hi @Anonymous,  there are some corrections needed in the logic you provided:
It seems you missed a parenthesis in the CALCULATE function for both variable C and D

In the SWITCH function, DAX does not directly support using the && operator. Instead, you should use the AND function.

Here's the corrected DAX for your conditional formatting:

Color Format = 
VAR a = SELECTEDVALUE('Table'[Attribute])
VAR B = SELECTEDVALUE('Table'[Values])  -- Corrected the column name here from Value to Values
VAR C = CALCULATE(SUM('Table'[Values]), FILTER('Table', 'Table'[Attribute] = "Actual"))
VAR D = CALCULATE(SUM('Table'[Values]), FILTER('Table', 'Table'[Attribute] = "Target"))
RETURN 
    SWITCH(TRUE(),
        AND(a = "Profit%", C = 0), "Red",
        AND(a = "Profit%", D = 0), "Orange",
        BLANK()
    )

After creating the Color Format measure, go to your table visual where you wish to apply the conditional formatting.
Select the column you want to format (in this case, the "Values" column representing the Profit%).
Click on the drop-down arrow next to the column and choose "Conditional formatting" > "Font color" (or "Background color", depending on your need).
Choose "Format by field value", and in the dialog box, pick the "Color Format" measure you just created.
Apply the changes, and your table should now color the cells in the "Values" column for "Profit%" according to the conditions provided.
This will apply the conditional formatting to the Profit% based on the Actual and Target values.

 

If you find this helpful, please provide a kudo and mark it as an accepted solution.