6 Replies Latest reply on Dec 13, 2013 9:33 AM by Juracy Americo

# Table calculation and color isn't working as expected

Hi everyone,

First, thank you to Rob Strong and Jim Wahl for the post Getting a count of Above and Below Averages

From that post I was able to come out with the workbook below.

I did't a calculation field "AboveOrBelowAverage" it is working fine, but for the first sales rep isn't .For the second sales rep to the end it is working fine.

I 'm missing something here , but I don't know what.

The desire is to color each period bar by blue when is Above or equal average and red when is Below average .

Any help would be much appreciated.

• ###### 1. Re: Table calculation and color isn't working as expected

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

Hi Jim,

That is what I'm missing ....Thank you very much.

• ###### 3. Re: Re: Table calculation and color isn't working as expected

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?

Any help would be much appreciated.

• ###### 4. Re: Table calculation and color isn't working as expected

Something is off with the calculation--as a quick hover will show you that it is returning that bar as "below average":

Note that your P02 bar is also showing a blue bar even though it is below the average.

• ###### 5. Re: Table calculation and color isn't working as expected

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

Hi Jim and Matthew,

Thank you very much for the quick reply.