Forum Discussion
Matrix Visual - Conditional Formatting Removed when Hierarchy Expanded
Is this expected or something I can work around?
I have setup a matrix in an income statement layout with a hierarchy for the rows. I have setup cell conditinal formatting to highlight rows that provide totals. The problem is that when the hierarchy is expanded to show the next level on ANY row all conditional formating on the top level rows goes away.
Before expansion:
After Expansion
OK! Here's the cause of the problem: I had the "Appy to" option set to "Values only". The solution is to set it to "Values and totals".
Right click on measure -> Conditional Formatting -> Background
Here's my guess at the reason behind this. With all items in the hierarchy collapsed each row shows values. When you expand even one hierarchy row all rows (except the newly visible lower level) are now TOTALS. The lower level that you just exposed shows values.
Hope this helps someone.
3 Replies
WZorn How are you doing the conditional formatting? Is it a measure or how is it configured?
Hi, Greg
Yes, I'm using a measure. A really simple one. I have index numbers associated with each element of the hierarchy. I also use them for sorting the row order. I have a measure that compares the index to a static number (the line I want highlighted). That measure returns the hex color code. I'm using the cell background conditional formating set to Field Value and referencing the measure.
Row Highlighting =SWITCH(MAX('IncState Line Items'[Measure Sort Order]),3, "#93C2EC",6, "#93C2EC",8, "#93C2EC",30, "#93C2EC",36, "#93C2EC",37, "#93C2EC")I've placed my measure in the values section and I can see the correct code appearing where it is suppose to, but the cells on that row are not being formatted.
OK! Here's the cause of the problem: I had the "Appy to" option set to "Values only". The solution is to set it to "Values and totals".
Right click on measure -> Conditional Formatting -> Background
Here's my guess at the reason behind this. With all items in the hierarchy collapsed each row shows values. When you expand even one hierarchy row all rows (except the newly visible lower level) are now TOTALS. The lower level that you just exposed shows values.
Hope this helps someone.
