cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Super User

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

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?

Did I answer your question? Mark my post as a solution! and hit thumbs up
1 ACCEPTED SOLUTION
Super User

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)

Did I answer your question? Mark my post as a solution! and hit thumbs up
6 REPLIES 6
Helper I

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

Super User

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)

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

Are you saying the documentation is not correct here?

Thanks,

Pragati

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Super User

@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."

Did I answer your question? Mark my post as a solution! and hit thumbs up