I need to conditionally format a dimension in a Privot Table, could somebody point me in the right direction?
My data looks very approximately like this:
I require conditional formatting to be placed on the dimension so that 1 and 3 are black and 2 is light grey, because of the value in the flag.
What I've tried so far:
1) Chart Properties>Dimensions>Used Dimensions -> expand the dimension and create the following expression under Text Color
This is completely ignored, as is =LightGray() or just Gray()
Why is this option even there if it doesn't work? Is there a switch somewhere I'm missing
2) Turn On Design Grid>Right Click in Cell>Select Custom Format Cell>Select Text Color>Base Color>Calculated and insert the same expression:
This time LightGray() on its own works, but the conditional expression does not.
Anyone know how I can acheive the conditional formatting I need? Looking through the archives I haven't found a satisfactory answer.
I'm on 11.20 SR5
Solved! Go to Solution.
If flag is associated with [dimension], but not necessarily with every combination of [dimension] and other pivot dimensions, then 'flag' in conditional expression could still evaluate to null and cause the test to fail - expression technically works,but you get the 0-flag color anyway. This is a part where the Only(TOTAL<dimension> flag) bit actually comes helpful. I'm attaching an example, hope it helps.
and it should be in
Chart Properties>Dimensions>Used Dimensions -> expand the dimension and create the following expression under Text colour
Hi Julian and Ashwani thanks for the reply.
- The expression I'm using
works if I place it in an expression rather than a dimension on the same pivot table. When I place it in a dimension, it is completely ignored, and the text is always black regardless of what is under Text Colour (if I set this to =Blue() or just Blue(), the text is still black)
Any ideas why this might be ignored? Am I missing a setting somewhere?
Perhaps your first dimension has several possible flag values. See attached example. When year 2009 is not selected the text color of the Product dimension is not changed. When year 2009 is selected there is only one year value per product and the text color of the products is changed.
Thanks for all your replies.
Sundarakumar: No there is no theme
Gysbert: There are only two possible flag values, 1 and 0.
Ashwani: I can't send an example I'm afraid as our data and implementation is very sensitive.
Something else weird I've just noticed, it depends very much on the selection. Say I take the conditionality out of the expression against the colour of the dimension altogether. Text Color is now set to LightGray()
In my grid, 5 and 6 are now light gray and the rest are black, I make a different selection, all are black, another selection, every other cell is light gray. There doesn't seem to be any pattern to whether the formatting is applied or not.
Have I uncovered a bug in the pivot table? My pivot table is vastly more complex than stated, I just wanted to convey the basic problem.