Calculating Utilization -- Multiple Employees; different Number of Weeks

[Anonymous]

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%

Name	Billable Hours	Entry Date	Week Number
Bob	4	1/03/19	1
Bob	6	1/16/19	3
Bob	3	1/22/19	4
Bob	2	2/17/19	8
Bob	1	2/18/19	8
Bob	1	2/22/19	8
Bob	3	3/02/19	9
Bob	6	3/10/19	11
Bob	5	3/14/19	11
Bob	4	3/29/19	13
Bob	7	4/15/19	16
Bob	1	4/29/19	18
Eric	3	3/03/19	10
Eric	1	3/06/19	10
Eric	1	3/20/19	12
Eric	3	3/22/19	12
Eric	5	3/27/19	13
Eric	1	3/30/19	13
Eric	4	4/03/19	14
Eric	5	4/16/19	16
Eric	2	4/27/19	17
Eric	4	4/29/19	18

I greatly appreciate any assistance with this.

Thank you.

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous · Accepted solution]

@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

[Anonymous]

Thank you Nathan,

Using the guidance you provide I was able to modify thigs to meet my needs. It worked!! Many, many thanks for your assistance!!