Conditional Formatting of a Dimension in a Pivot Table

[justin_morley]

Hi,

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:

dimension	flag	expression
1	0	87
2	1	56
3	0	12.1

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

=if(flag=1,LightGray(),Black())

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:

=if(flag=1,LightGray(),Black())

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

Thanks,

Justin

[juleshartley]

Hi,

This should work. Have you tested that your condition is working correctly outside of the formatting expression?

[ashwanin]

Can you please try this

=if(Dimension=2,LightGray(),Black())

[ashwanin]

and it should be in

Chart Properties>Dimensions>Used Dimensions -> expand the dimension and create the following expression under Text colour

[justin_morley]

Hi Julian and Ashwani thanks for the reply.

- The expression I'm using

=if(flag=1,LightGray(),Black())

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?

[sundarakumar]

R u using any theme on ur app?

[ashwanin]

Hi Can you upload the document , bz these conditions are working fine here.

[sundarakumar]

PFA app, the conmdition is working fine,

Pease upload sample app for clarification..

-Sundar

[Gysbert_Wassenaar]

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.

[justin_morley]

Hi,

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()

dimension	flag	expression
1	0	87
2	1	56
3	0	12.1
4	0	...
5	0	...
6	0	...

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.