Aggregate Average?

[#1]

Hi,

Can someone help me with below.

I have a formula that sums number of hours across a period of time.

I am dividing this by the unique employees over the same period so I can see the average amount of hours worked per employee.

Attached is basically the output. As you can see, there is a gap of 2. Can someone help me explain what I can do to fix this?

The only formula I'm using is sum(hours)/count(distinct(employees)).

This issue is, the line chart does not total to 62 but rather 64.

You can see the unique employees for each month. The total unique employees is 127 (perhaps some more individuals were added/removed each month).

[JonnyPoole · Accepted solution]

One thought:

count( distinct employees)  will count the unique employees over the 3 months.  If the same employee was in more than one month, it will NOT double count that employee.  Is that what you want ?

To count unique employees in each month and then add up the monthly totals you could modify to

count( distinct   Employees&Month) 

... so that if the same employee was in multiple months , they would EACH count toward the grand total.

sum(hours) will not have this issue

[#3]

try this,

floor(sum(hours)/count(distinct(employees)))

[jagan]

Hi,

Try this expression to get month wise employee count and sum upto total

count( distinct   Employees&Month)

OR

Sum(Aggr(Count(Distinct Employees), Month))

Hope this helps you.

Regards,

Jagan.

[jonathandienst]

Hi

This is because you are trying to add ratios with different divisors (unique employees). In other words, the sum of the ratios (64) is not the same as the ratio of the sums (62). To sum the ratios, use this:

     Sum(Aggr(sum(hours)/count(distinct(employees)), MONTH))

where you must replace MONTH with the correct month field name.

HTH

Jonathan

[#6]

Thanks everyone.

I ended up using sum(WeeklyHours)/count(distinct(Employees&[Dimension]))