
1. Re: Table calculation and color isn't working as expected
Jim Wahl Dec 12, 2013 6:38 AM (in response to Juracy Americo)Hi Juracy,
It looks like a rounding issue. This, for example, seems to work:
AboveOrBelowAverage =
IF ROUND(SUM([Percent]),3) >= ROUND(WINDOW_AVG( SUM([Percent])),3) THEN "Above average" ElSEIF ROUND(SUM([Percent]),3) < ROUND(WINDOW_AVG( SUM([Percent])),3) THEN "Below Average" END
Note you can simplify this a bit:
IIF(ROUND(SUM(Percent),3) >= ROUND(WINDOW_AVG(SUM(Percent)),3), "Above average", "Below average")
Jim

2. Re: Table calculation and color isn't working as expected
Juracy Americo Dec 12, 2013 11:26 AM (in response to Jim Wahl)Hi Jim,
That is what I'm missing ....Thank you very much.

3. Re: Re: Table calculation and color isn't working as expected
Juracy Americo Dec 13, 2013 7:50 AM (in response to Jim Wahl)Hi Jim, me again.
Now I'd like to see the same red an blue bars without sales rep, so I got rid of it and I can have the total
But I noticed something strange, even with the calculated field that are taking care of the rounding issue, I don't know why the last period bar are not blue once it is above average.
Can you guide what I'm missing here please?

AboveOrBelowAverage.twbx 141.4 KB


4. Re: Table calculation and color isn't working as expected
Matt Lutton Dec 13, 2013 8:01 AM (in response to Juracy Americo) 
5. Re: Table calculation and color isn't working as expected
Jim Wahl Dec 13, 2013 8:19 AM (in response to Juracy Americo)Hi Juracy,
The problem is that you're using an AVG(Percent) aggregation in the view and a SUM(Percent) aggregation in the formula for AboveOrBelowAverage.
Note that the reference line is computed as TOTAL(AVG(Percent), which is slightly different than WINDOW_AVG(AVG(Percent). TOTAL() aggregates at a higher level of detail and in the case averages all of the percent values, regardless of period. WINDOW_AVG(AVG(Percent)) averages the average of each period.
In this case they're similar. In others, of course, they could differ significantly.
In any case, the AboveOrBelowAverage needs to of course match the aggregation and reference line calculations in the view. Here's how I modified it to match your Row pill AVG(Percent) and reference line using TOTAL().
IIF(ROUND(AVG(Percent),3) >= ROUND(TOTAL(AVG(Percent)),3), "Above average", "Below average")
Jim

6. Re: Table calculation and color isn't working as expected
Juracy Americo Dec 13, 2013 9:33 AM (in response to Jim Wahl)Hi Jim and Matthew,
Thank you very much for the quick reply.