Calculating aggregated employee utilization

# Calculating aggregated employee utilization

• Thursday, August 16, 2012 9:02 AM

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

### All Replies

• Monday, August 27, 2012 7:19 PM

Hi Frederik

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

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

• Monday, September 03, 2012 9:01 AM

Hi Javier

Great idea. I will work on it.

Best regards

Frederik