Background color for Pivot Table cells when cell value exceeds aggregate value

[bvikramreddy89]

Hi,

Am trying a possibility for setting background color for pivot table cells when cell values greater than "avg + Std" and less than "avg - std" of individual days of month over that year i.e.

Cells of Jan 1st, Feb 1st ... Dec 1st shoud be compared with "Avg + Std and Avg-Std" of these 12 values. Similarly,
2nd of every month and 3rd of every month, so on till 31st for that year.

I have Days(Vertical) of month from 1 to 31 as one dimension and year-month(Horizontal) as other dimension. Have filled data cells.

Am using below but its not working:

if(value >vAvdStdPlus or  value<vAvgStdMinus, Lightred(), Green())

Where:
vAvdStdPlus = aggr(avg(value),Month_day, Year) + aggr(StDev(value),Month_day, Year)
vAvgStdMinus = aggr(avg(value),Month_day, Year) - aggr(StDev(value),Month_day, Year)

Am attching, table format in excel . Please help me with this.

[Kushal_Chawda]

@bvikramreddy89  try below expression

//change variable expressions as below (include "=" )
vAvdStdPlus 
= sum(aggr(avg(value),Month_day, Year)) + sum(aggr(StDev(value),Month_day, Year))

vAvgStdMinus 
= sum(aggr(avg(value),Month_day, Year)) - sum(aggr(StDev(value),Month_day, Year))

// Now you can try below expression in background color expression
if(sum(value) > $(vAvdStdPlus) or  sum(value)<$(vAvgStdMinus), Lightred(), Green())

[bvikramreddy89]

Hi Kush,
Thank you for the reply but i already have tried that and its not worked.
The expression is not applying to all the cells but only to few. Thats the main issue.

Does anyone worked out similar requirement. Can anyone suggest any other possibility.

[sunny_talwar · Accepted solution]

How about you try this

Avg(TOTAL <Month_day> value) + StDev(TOTAL <Month_day> value)
Avg(TOTAL <Month_day> value) - StDev(TOTAL <Month_day> value)

[bvikramreddy89]

Hi Sunny,

Thank you very much. The expression worked.