It looks like a rounding issue. This, for example, seems to work:
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")
That is what I'm missing ....Thank you very much.
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
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")
Hi Jim and Matthew,
Thank you very much for the quick reply.