Forum Discussion

joku50's avatar
joku50
Frequent Visitor
3 years ago

Matrix - conditional formatting in comparison with another row

Hi all, appreciate your time to read this question first of all.

 

I am trying to implement conditional formatting based on another row within the filter context but I can't get my head around how to do it.

I have a matrix structured as below screenshot. How I need to come up with this is:

*** Slicers are available for Category, Parameter, Plan/Actual, and Period
1) conditional formatting to Recruitment - Recruitment A - Actual
---> IF Actual is blank, no color coding, IF Actual is bigger than Plan(right above row), Green, if smaller, Red)

2) conditional formatting to Drops - DropA - Actual 
---> same rule as above - IF Actual is blank, no color coding, IF Actual is bigger than Plan(right above row), Green, if smaller, Red)

 

I have been trying to write VARs and RETURN after searching for numerous solutions, but I guess I am not writing it in a proper way. Managed to return to color values in the rows but the color coding rule is not correctly reflected(e.g. everywhere it is marked as green).

Can you please help with this? Your response would also help me to learn about how to write DAX properly in this case.

Appreciate your support in advance.

 

**** Added how my data looks like

 

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    joku50 you need to share what your data model looks like? How tables are connected to provide the solution.

    • joku50's avatar
      joku50
      Frequent Visitor
      3 years ago

      parry2k  Hello, yes I have just added another screenshot to explain that in my original post. Could you please take a look at it? Thank you in advance.

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    joku50 add a measure for color and use that in conditional formatting, tweak the measure as you see fit.

     

    Sum Value = SUM ( Table[Value] )

Color = 
VAR __plan = CALCULATE ( [Sum Value], Table[Plan/Actual] = "Plan" )
VAR __actual = CALCULATE ( [Sum Value], Table[Plan/Actual] = "Actual" )
RETURN
SWITCH ( 
    TRUE (),
    ISBLANK ( __actual ), BLANK (),
    __actual > __plan, "Green",
    "Red"
)