CODEWITHSUNDEEP
powerbidaxconditional-formatting
SRJCoding
SRJCoding

Reputation: 473

How to apply conditional formatting to matrix rows created by a calculation group?

I have a matrix in my report that looks like this:

KPI_1 KPI_2 KPI_3
Total 100 75 200
Total_last_week 80 100 200
Growth_vs_last_week 25% -25% 0%

The column headers (KPI_1, KPI_2, and KPI_3) are all measures, which are placed in the 'values' section of the matrix.

The rows (Value_current, Value_last_week, and Growth_vs_last_week) are all calculation items within a calculation group. This calculation group is placed in the 'rows' section of the matrix. The calculation group has been created through Power BI itself, not Tabular Editor.

I want to add conditional formatting to the Growth_vs_last_week row only, to show whether growth is positive, negative, or neutral. So in the example above, the values in that row should be coloured green, red, and amber respectively.

Does anyone know how to do this please?

Upvotes: 0

Views: 266

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

This one is a little tricky and we can tackle it with another Calculation Group with a higher precedence.

Step 1 - Create three new measures

CF KPI_1 = BLANK()
CF KPI_2 = BLANK()
CF KPI_3 = BLANK()

We only need these to capture the MEASURENAME as the Conditional Formatting is being calculated.

Step 2 - Create a new Calculation Group

Create a new Calculation Group and set its Precedence to be higher than your existing one. Only need one Calculation item for this:

CF KPI item = 
  SWITCH( TRUE(),

    // if not one of these then pass through
    NOT ISSELECTEDMEASURE([CF KPI_1], [CF KPI_2], [CF KPI_3]), 
      SELECTEDMEASURE(),

    // below onwards is for Conditional Formatting only

    // for the row that we don't need CF - return BLANK()
    SELECTEDVALUE(YourCalcGroup[YourCalcGroup column]) <> "Growth_vs_last_week",
      BLANK(),

    // switch-out to proper measures
    ISSELECTEDMEASURE([CF KPI_1]), [KPI_1],
    ISSELECTEDMEASURE([CF KPI_2]), [KPI_2],
    ISSELECTEDMEASURE([CF KPI_3]), [KPI_3]
)

This new calc group should look like this and appear on top of yours.

enter image description here

Step 3 - Apply!

To activate this new Calculation Group to your visual, you will need to add it to the Visual filter and select the Calculation item.

enter image description here

Finally, you can apply the Conditional Formatting to each of your columns in the visual, using [CF KPI_1], [CF KPI_2], or [CF KPI_3] respectively.

For testing/debugging and for what we are aiming to achieve is:

enter image description here

Where the CF * measures return the same values for the desired row only.

Upvotes: 0

Related Questions