Forum Discussion
Employee Utilization - incorrect totals
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!
Hi tpal415 ,
Is your issue solved now?
Best Regards,
Kelly
- tpal4156 years agoFrequent Visitor
Kelly Unfortuantely no. I believe my issue is with calucalting total hours available. I can't quite figure out how to calculate the total work hours available by each individual employee when employees have different start dates. I have a calendar with the total work hours and another table with employee start dates, but cannot tie the two together. The billable hours is correct, and totals are all summing correclty, but below are the outputs:
Total Hours:
#TotalHours = VAR __Start = MAX('7 People'[Start Date]) RETURN switch(max('2 Hours'[Employee Type]), "Full-Time", SUMX(FILTER('Work Hour Calendar',[Date]>__Start),'Work Hour Calendar'[WorkHours]), blank() )Output:
*Here is the issue - it's good that only the numbers are showing up in months where people have input hours, but again it's taking the entire month of work hours, not by day and also only months 7-12 which is most likely because the most recent (max) start date was mid July. This obviously throws everything else off.
Total Hours by Employee:
#TotalHoursByEmployee = SUM('Work Hour Calendar'[WorkHours])Output:
Total Billable Hours:
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] ) )Output:
Utilization:
%Utilization = VAR __utilization = DIVIDE([Total Billable Hours],[#TotalHours],0) VAR __days = countrows('2 Hours') RETURN IF(ISINSCOPE('7 People'[Employee Name]), IF( Isblank(__Days), BLANK(), IF( ISBLANK(__utilization), 0, __utilization )), VAR __tempTable = SUMMARIZE( '2 Hours', '2 Hours'[Employee Name], "__billableHours",[Total Billable Hours], "__totalHours",[#TotalHours] ) VAR __totalBillableHours = SUMX(__tempTable,[__billableHours]) VAR __totalTotalHours = SUMX(__tempTable,[__totalHours]) RETURN DIVIDE(__totalBillableHours,__totalTotalHours,0) )Output:
- v-kelly-msft6 years ago
Community Support
Hi tpal415 ,
It's a subtotal issue,a very common issue,could you pls upload your .pbix file to Onedrive business and share the link with me?(Remember to remove the confidential information)
Best Regards,
KellyDid I answer your question? Mark my post as a solution!