Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
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 -
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
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!!
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
157 | |
119 | |
104 | |
77 | |
69 |