Conditional formatting rules for totals and subtotals in Matrix and Table Visual

[Fowmy]

The Conditional Formatting document says,

"You can apply conditional formatting rules to totals and subtotals, for both table and matrix visuals", but you must manually set the thresholds and ranges for conditional formatting rules under Minimum and Maximum instead of Lowest and Highest values based on the measure I provide

Has anybody found a way to use the measure to set the limits?

[JanaP]

Hello, 

I am stuck on what I understood was the original question - Has anybody found a way to use the measure to set the limits?

I am buffled as to why one has to do this - can PBI not work out the min and max from the visible values? 

I have a matrix with several columns and 3 to 5 rows, and only showing the highest level of hierarchy. The range of values varies greatly depending on the slicer selections. So setting a rigid min and max values is not really an option. 

I have searched and found only a handful of mentions but with no solution.

Any thoughts on that?

Thanks a lot guys 🙂

Jana

[Fowmy · Accepted solution]

As per the documentation on CF, https://docs.microsoft.com/en-gb/power-bi/create-reports/desktop-conditional-table-formatting , it forces you to specify the Min and Max when you select the Values and Totals option in the Apply to dropdown. 

I managed to figure it out and solve my problem by collapsing the entire level in the Matrix Row Hierarchy. The point to note here is that you will not see CF application on the parent level of the hierarchy if any child level is expanded. you need to close all child levels. Refer to this example below.

#1 with all levels collapsed, CF works here.
#2 One child-level expanded inside
#3 Expanded child level (Country causing the CF to stop)

[Pragati11]

HI @Fowmy ,

Are you talking about using a measure something like below:

conditionalColor = 
var cost = SUM('Supermarket Sales'[Total price])
RETURN
SWITCH(
    TRUE(),
    cost >= 10 && cost <= 250, "#FF3333",
    cost > 250 && cost <= 500, "#FF9933",
    "#009900"
)

I have used the above measure to add a conditional Background to total price values and totals as shown below:

Let me know if I have mis-understood your query.

Thanks,

Pragati

[Fowmy]

Thanks, @Pragati11  for your reply,

However, I was trying to solve a different issue here. I will post my explanation

[Pragati11]

@Fowmy ,

Are you saying the documentation is not correct here?

Thanks,

Pragati

[Fowmy]

@Pragati11 

Not exactly,
As per my understanding, the documentation does not give enough information and clarify on the following note

"You must manually set the thresholds and ranges for conditional formatting rules. For matrices, Values will refer to the lowest visible level of the matrix hierarchy."