Thursday, August 16, 2012 9:02 AM
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.
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:
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. :)
Monday, August 27, 2012 7:19 PM
Try the following formula instead:
SUM( Activity_Fact[Hours] ) / SUMX( VALUES( Activity_Fact[Employee] ), DISTINCTCOUNT( Activity_Fact[DateKey] ) * 8 )
Monday, September 03, 2012 9:01 AM
Great idea. I will work on it.