Forum Discussion
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.
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)
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.
3 Replies
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.
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!
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.
