Forum Discussion

davidz106's avatar
davidz106
Icon for Helper III rankHelper III
2 years ago
Solved

Conditional formatting of Matrix cells based on previous row value

I'm working with a matrix table in Power BI containing 15 columns. I need to apply conditional formatting to highlight cells if their value differs from the value in the preceding row . I've added an index column to facilitate the comparison. How can I achieve this without pivoting the table? Thank you!

Here it is what I want to achive. 


If this formatting could only be applied to lower level (1.1, 2.1), it would be even better for my use case. Here is the example of that:

 

Sample dataset: https://we.tl/t-vHI2J3u0CC

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi davidz106 ,

    Based on my testing, please try the following methods:

    1.Create the index column.

    2.Create the new measure to highlight the values.

    Difference = 
   VAR CurrentValue = SELECTEDVALUE('Table'[Median of Speed])
   VAR _Index = SELECTEDVALUE('Table'[Index])
   VAR PreviousValue = CALCULATE(MAX('Table'[Median of Speed]), FILTER(ALL('Table'), 'Table'[Index] = _Index - 1))
   RETURN
   IF(CurrentValue <> PreviousValue, "yellow", "white")

    3.Select the Median of Speed column and choose conditional formatting > Background color.

    4.Select Field value and choose the measure.

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

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

    • davidz106's avatar
      davidz106
      Icon for Helper III rankHelper III
      2 years ago

      Very well. But is there a way the first value (100 inside AP1-1.1) does not get highlighted because technically from my point of view it does not have a "previous row". 

    • davidz106's avatar
      davidz106
      Icon for Helper III rankHelper III
      2 years ago

      Got it.

       

      Difference =
      VAR CurrentValue = SELECTEDVALUE('Table'[Median of Speed])
      VAR _Index = SELECTEDVALUE('Table'[Index])
      VAR PreviousValue = CALCULATE(MAX('Table'[Median of Speed]), FILTER(ALL('Table'), 'Table'[Index] = _Index - 1))
      RETURN
      IF(
          _Index = 1,
          "white",
          IF(CurrentValue <> PreviousValue, "yellow", "white")
      )