Calculating aggregated employee utilization

Question

Hi guys

I'm new to PowerPivot and is struggling with a calculation. I can calculate the individual employees hour utilization, but I can't figure out how to calculate the aggregated hour utilization.

Utilization = HoursWorked / WorkingHoursAvailable

Data Model Sample

The amount of work hours are calculated individually for each employee.  It's the sum of WorkHours from first to last day of activity. Joe thus have 3 workdays and Martin has 2.

Calculations:

Joe = (3+5+8+4) / (8+8+8) = 83,3%

Joe & Martin = ((3+5+8+4)+(8+8)) / ((8+8+8)+(8+8)) = 83,3%

My best attempt in PowerPivot is:

=SUM(Activity_Fact[Hours])/
CALCULATE(SUM(Calendar[WorkHours]); FILTER(Activity_Fact; Activity_Fact[Employee]))

When calculating Joe & Martin combined, it comes out with:

Joe & Martin= ((3+5+8+4)+(8+8)) / (8+8+8) = 150%

I've tried using SUMMARIZE() to aggregate per employee, but didn't get it to work.

I hope you can help. :)

Best regards

Frederik

Answer 1

Hi Frederik

Try the following formula instead:

SUM( Activity_Fact[Hours] ) / SUMX( VALUES( Activity_Fact[Employee] ), DISTINCTCOUNT( Activity_Fact[DateKey] ) * 8 )

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Hi Javier

Great idea. I will work on it.

Best regards

Frederik