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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
KennethN
Frequent Visitor

How can I set different Conditional Formatting rules for columns and for totals?

Hey folks, been banging my head against the wall with this for hours and still can't quite figure it out.

I have a DAX table with 4 columns (put in excel to make it easier to see). This table is put into a simple matrix table with Name set as the row, the columns set to Training Category and the values as Total Training Hours. A slicer is applied that allows the user to select the FiscalYear.

 

image.png

 

I'm trying to set conditional formatting so that, if any of the column totals are 2 or greater, that cell would be highlighted in Green, if they are lower than 2, they'll be highlighted in Red.

 

Alternatively, for the Totals Column (which is generated by the visual), i would like to highlight that total green if the value is 10 or greater, or highlight red if under 10.

 

In excel this is quite easy, as i can just set a different conditional format rule for the totals column, apart from the other columns; however, with Bi, I am only able to setup rules based on a single field. I've looked online and have seen some talks of using a couple of measures that outputs a 1 or 0 to indicate what color the cell should be but I can't quite get it to work. But if I use 2 measures in the value field, it adds additional columns to the table and becomes unreadable.

 

Any ideas on how I can pull this off? (In the image below you can see that i can get it to work for the columns, but then for the Total, it's highlighting anything above 2)

 

MatrixVisual.jpg

 

1 ACCEPTED SOLUTION
audreygerred
Super User
Super User

Hi! You can create separate measures for each of the items that would be taken care of through the columns - this will allow you to set a rule for each individually, including your total.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
danextian
Super User
Super User

Hi @KennethN 

 

Use  this as a field value backgrouund color conditional formatting.

SWITCH (
    TRUE (),  
    
    -- Check if no single value is selected for 'table'[category].
    NOT ( HASONEVALUE ( 'table'[category] ) ), 
        -- If no single value is selected:
        -- If the total is greater than or equal to 10, return "green/hex/rgba/rgb".
        -- Otherwise, return "red/hex/rgba/rgb".
        IF ( [total] >= 10, "green/hex/rgba/rgb", "red/hex/rgba/rgb" ),
    
    -- Default condition when a single value is selected:
    -- If the total is greater than or equal to 2, return "green/hex/rgba/rgb".
    -- Otherwise, return "red/hex/rgba/rgb".
    IF ( [total] >= 2, "green/hex/rgba/rgb", "red/hex/rgba/rgb" )
)

hex/rgba/rgb - you may use hexadecimal, RGB or RGBA values or the standard colors (red,green,black yellow, some literal word colors are not recognized)

NOT( HASONEVALUE )- technically at the total column level, a column or category has more than one values so this returns true, unless of course, a column only has one distinct value or filtered down to just one value. Alternatively, you can use ISFILTERED.

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
audreygerred
Super User
Super User

Hi! You can create separate measures for each of the items that would be taken care of through the columns - this will allow you to set a rule for each individually, including your total.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Wow. It's amazing how you can be staring a problem in the face for so long, that you forget to take a step back and see that there's a really simple solution. 
For my data, the three TrainingCategories haven't changed in a couple years so creating three seperate measures for each, and then a 4th measure to handle totals was exactly what I needed. I can apply seperate conditional formatting for each column, which is great if the requirements per category ever change in the future. 
Thank you so much for the simple, painless solution!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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