Forum Discussion
Employee Utilization - incorrect totals
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:
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)
Kelly