Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fowmy
Super User
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?

Fowmy_1-1634732757795.png

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

1 ACCEPTED SOLUTION
Fowmy
Super User
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)

Fowmy_0-1634737600871.png

 

   



 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
JanaP
Helper I
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

Fowmy
Super User
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)

Fowmy_0-1634737600871.png

 

   



 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Pragati11
Super User
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:

Pragati11_0-1634735371659.png

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

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

Appreciate your Kudos!!

Proud to be a Super User!!

Thanks, @Pragati11  for your reply,

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy ,

 

Are you saying the documentation is not correct here?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

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

Appreciate your Kudos!!

Proud to be a 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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.