Trendline AVG Calculation

[Anonymous]

Hello,

the attached example shows the worked hours made by some employees; in the chart is shown the calculated AVG (the green reference line) and the Average trend line (the red one).

The sum of the hours is 60 (according to the hours line of the graph), so the right result for the average is 15 (as shown by the AVG formula).

Could be someone so kind to explain me the average 20 result by the trendline calculation?

Thank you.

N.

[Anonymous]

In the attached example I used a bar chart, but the average results are the same. 😞

[Anonymous]

Nope if you have two dates equal themselves with two different hour values

[sunny_talwar · Accepted solution]

I know you are not satisfied with the average which is calculated, but that is how it is... to read more on the topic of averages, you can read here:

Average – Which average?

[Anonymous]

it's because you shouldn't really use a 'trendline'.  for that kind of dimension, you should use a reference line.

it may not be what you want it to do, but it's summing your expression total (60) and dividing by your unique dimension values.  Which if you look at the values in the chart display, that is what you're presenting.

[isorinrusu]

Hi Nicola,

It does what the aggr(...) formula does. It calculates the mean for Employee after summing the hours (i.e. your expression) for each Employee.

So the trendline is calculated there as (15+20+25)/3.

If your expression was avg(Hours), the trending line would be calculated as (15+10+25)/3.

Regards,

Sorin.

[Anonymous]

Thank you to everybody for your explanations!

N.