Background color in pivot

[#1]

Hi,

My case is different from normal coloring of cell background.

Account wise avg is 5. If individual Eng's avg is less than Account's Avg, background will be Red, else Green.

Its perfect till no selection made.

No Selection
Account	Eng	Month	Avg
			5
Acct A	Eng A	Feb-10	4
Acct A	Eng A	Mar-10	4
Acct A	Eng B	Feb-10	6
Acct A	Eng B	Mar-10	6
Eng A selected
Account	Eng	Month	Avg
			4
Acct A	Eng A	Feb-10	4
Acct A	Eng A	Mar-10	4

If I select a engineer, eg I selected Eng A above, then Account's avg will also be 4...Now Eng A is in Green Background. I still want it in Red ie it should be compared to Account A's avg 5.

Any Suggestion!!

[brenner_martina]

Hi, Arun,

can you please upload an example or tell use the expression for the background color

[#3]

Hi Martina,

M using below expression

If(Avg(Value) < Avg(Total <Account, Month> Value),RGB(255,0,0), RGB(0,255,0))

[brenner_martina]

Hi, Arun,

you have to change your expression, so that you have only the first dimension in the Total-expression, p.e.

If(Avg(Value) < Avg(Total <Month> Value) )
or
If(Avg(Value) < Avg(Total <Account> Value) )

[sparur]

Hello, arun,

strange, but I check you situation and it worked perfectly.

When I select Eng A background is changed into Green

[#6]

Hi Arun,

You should use AGGR function... what you want is (at least I think so;):

if (Avg(Value) < aggr(avg(Value), Account, Month), RGB(255,0,0), RGB(0,255,0))

Is Eng also a dimension - should you also include it in the aggregation dimension ?

If you could post you app here, we could help you 😉

Regards,

Miha

[#7]

Dear All,

All suggestions are not working.

It is a requirement where we need to put set analysis i suppose. Such that Avg will everytime be 5 only regardless of any selection.

Wud like to clear one thing here. Selection for Engineer should only be neglected. Because I can select a particular department for a account. Then all engineers' avg will be compared with the avg for that department only.

[sparur]

can you post your qvw?

[#9]

attached is the qvw. I need Eng A in red only.

[sparur · Accepted solution]

may be like this...