Forum Discussion
Employee Utilization - incorrect totals
I am trying to figure out how to build a formula that will provide the utilization for multiple employees reporting different billable hours and also with different working hours due to when they started. Hours are reported by day with multiple employees in the same column and hours in another column. I've calculated working hours within a master calendar table.
I'm trying to divide billable hours by working hours, using the calendar to dynamically filter by month, quarter, etc. Ideally I would like to use this to have each employee on a row with utilization and then the total being entire utilization.
Below is the utilization table, but totals are incorrect calculated as such:
- Employee Utilization = iferror([Billable Hours] / sum('Calendar'[WorkHours]), blank())
- Billable Hours = CALCULATE(SUM('2 Hours'[Logged Hours]),FILTER('2 Hours',[Billable Status]="1"))
- WorkHours = VAR WeekDayNum = WEEKDAY ('Calendar'[Date] )RETURN (IF (OR ( OR ( WeekDayNum = 1, WeekDayNum = 7 ), RELATED ( '6 Holidays'[Holiday Start Date[Date] ) <> BLANK ()),0, 8))
The row values seem to be very close, but I believe it is calculating each employees billable hours by the total available hours and not totaling correctly.
7 Replies
- Greg_Deckler
Community Champion
tpal415 - I have a utilization recipe in my book, DAX Cookbook, it is Recipe 1, Chapter 8.
https://github.com/gdeckler/DAXCookbook
If you can post sample data that would help.
Looks like you measure total is off. 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/547907Finally, since I see you are a New Member, please check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. Greg_Deckler Thank you for the quick reply! I utilized your recipe and it worked great, the totals are correct and much easier. My only issue is if an employee had not started it still divides total billable hours by total work hours, resulting in inacurratly low utilization. How can I filter out my total working hours for each unique individual employee?
Here are the calculations I used from your recipe:
% Utilization = VAR __Table = SUMMARIZE('2 Hours','2 Hours'[Employee Name]) VAR __TotalBillableHours = SUMX(__Table,[Total Billable Hours]) VAR __TotalHours = SUMX(__Table,[Total Hours]) VAR __Utilization = DIVIDE(__TotalBillableHours,__TotalHours,0) RETURN IF(ISBLANK(__Utilization),0,__Utilization)Total Billable Hours = var _Category = MAX('2 Hours'[Billable Status]) Return IF( _category = "0", 0, SUMX( FILTER( '2 Hours','2 Hours'[Billable Status] = "1" ), '2 Hours'[Logged Hours] ) )Total Hours = switch(max('2 Hours'[Column]), "Full-Time", SUM('Work Hour Calendar'[Work Hours]), blank() )Here is the result:
Employee Name 1 2 3 4 5 6 7 8 9 10 11 12 Total Employee 1 69% 61% 66% 100% 92% 103% 58% 81% 6% 0% 0% 0% 54% Below is my data:
People ID Project ID Logged Date Task Logged Hours Billable Status Employee Name Employee Title Employee Hourly Rate Employee Status 17187664 2970412 Thursday, January 2, 2020 2. Fieldwork 3 1 N/A Analyst 200 Full-Time 262732 2849029 Thursday, January 2, 2020 02. Business Development 4 0 N/A Managing Director & Co-Founder 300 Full-Time 262732 2849029 Thursday, January 2, 2020 0. Admin 4 1 N/A Managing Director & Co-Founder 300 Full-Time 262744 2854215 Thursday, January 2, 2020 4. QA 6 1 N/A Director 275 Full-Time 442154 2691602 Thursday, January 2, 2020 2. Internal Audit 0 1 N/A Manager 250 Full-Time 17055619 2855539 Thursday, January 2, 2020 Onsite Audit Support 8 1 N/A Consultant 225 Full-Time 17090027 2856177 Thursday, January 2, 2020 2. Internal Audit 0 1 N/A Senior Analyst 225 Full-Time 17187664 2982088 Thursday, January 2, 2020 3 1 N/A Analyst 200 Full-Time 262744 2856293 Thursday, January 2, 2020 1. Project Planning 6 1 N/A Director 275 Full-Time 17187664 2849029 Thursday, January 2, 2020 0. Admin 2 0 N/A Analyst 200 Full-Time 17187664 2855500 Thursday, January 2, 2020 2. Fieldwork 1 1 N/A Analyst 200 Full-Time 17168871 2856293 Thursday, January 2, 2020 2. Internal Assessments 0 1 N/A Analyst 225 Full-Time 17090027 2849029 Thursday, January 2, 2020 8 1 N/A Senior Analyst 225 Full-Time Thank you for any help, this has already got me on the right track!
Still haven't quite figured out how to incorporate start and end dates into total work hours for each employee using your utilization formula. I bought and read through the utilization chapters within your book, but cannot see an explanation on how to calculate work hours by individual employee, only an aggregation of total possible working hours. Any help would be appreciated!
- Greg_Deckler
tpal415 - See if this helps, attached PBIX from my other book, Learn Power BI which goes through the whole utilization stuff in detail.
I am thinking maybe:
Total Hours = VAR __Start = MAX('Employee Table'[Start Date]), RETURN switch(max('2 Hours'[Column]), "Full-Time", SUMX(FILTER('Work Hour Calendar',[Date]>__Start),[Work Hours]), blank() )Something like that.
