Avg in background color of expression

[#1]

Hi, i want to show a different background color if number of meetings per week is over or below let's say 30% of the average per week. I can't find a way to get the right expression in the background color for the expression in a pivot table. Help much appreciated, Zach.

[Gysbert_Wassenaar]

See attached qvw. The solutions uses a variable to store the 30% of average value. The background color expression uses the variable for the calculation.

[#3]

Hi, thanks for your help. I'm not there yet. What i need is an average calculation per row (per RoomID) over the weeks. For example: for RoomID 100 the numbers 10, 4 and 3 (the first three weeks) should have the red background color. This, since the average is 6 (24/4) and the upper bandwidth is 6*1.3=7.8 and the lower bandwidth is 6*0.7=4.2. So only the 7 (week 2013-5) should have a normal background color. Is this doable, that each row has it's own average inside the pivot table? Thanks again, Zach

[Gysbert_Wassenaar · Accepted solution]

Try this expression:

if(count(MeetingID)> 0.7* avg(total <RoomID> aggr(count(MeetingID),RoomID,Calender.YearWeek))

and count(MeetingID)< 1.3* avg(total <RoomID> aggr(count(MeetingID),RoomID,Calender.YearWeek))

  ,color(2))

[#5]

Hi, perfect. I wouldn't have come up with this formula! Thanks, Zach.