Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating Utilization -- Multiple Employees; different Number of Weeks

I am trying to figure out how to build a formula that will provide the utilization for multiple employees with reporting in a different number of weeks.

 

In the table below, Bob worked Jan - Apr, and Eric worked Mar - Apr. Some weeks they had billable hours, some weeks they did not.

 

If I look at Bob's billable hours for weeks 1 to 18 and base billable utilization on a 40-hour work week, we use -- SUM of Billable Hours/(# of weeks x 40 hours); 43/(18 x 40) = 5.97%. For Eric, his math is (29/(9 x 40)) = 8.06%.

 

But, if I want to add Bob and Eric together and look at the combined utilization for Bob's 18 weeks and Eric's 9 weeks, I need a formula that will sum billable hours and divide by the number of people that made entries for billable hours x the count of weeks they made entries x 40 hours per week. (NOTE: Weeks with 0 billable hours are not in the table but we are assuming that they made non-billable entries in these weeks. for example, Bob in week 10 and Eric in week 11).

 

I believe the "math" would actually be: (43+29)/((18 + 9) x 40) = 6.67%

 

NameBillable HoursEntry DateWeek Number
Bob41/03/191
Bob61/16/193
Bob31/22/194
Bob22/17/198
Bob12/18/198
Bob12/22/198
Bob33/02/199
Bob63/10/1911
Bob53/14/1911
Bob43/29/1913
Bob74/15/1916
Bob14/29/1918
Eric33/03/1910
Eric13/06/1910
Eric13/20/1912
Eric33/22/1912
Eric53/27/1913
Eric13/30/1913
Eric44/03/1914
Eric54/16/1916
Eric24/27/1917
Eric44/29/1918

 

I greatly appreciate any assistance with this.

 

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous - 

    Try this Measure:

    Util = 
var summary = SUMMARIZE(util, Util[Name], Util[Week Number], "Hours", SUM(Util[Billable Hours]), "Weeks", 1)
var hours = SUMX(summary, [Hours])
var weeks = SUMX(summary, [Weeks])
return DIVIDE(hours, (weeks * 40))

    Hope this helps,

    Nathan

3 Replies