I am working on a utilization report that has a data model with 2 tables. Table 1 = Data (all hours worked by an employee), Table 2 = EmpCalc (standard hours base for an employee).
My utilization measure is as follows: =sum(Data[Jan-19])/sum(EmpCalc[Jan])
I then created a Pivot which is has the following fields... Employee Type, Department, Employee Name. For each employee, the utilization measure is calculating correctly but when I subtotal by Department, the utilization percentage is way lower. It appears it is taking all the hours in the specific employee type/department but then dividing it by all standard hours in that department ignoring that employee type is in front of department in the pivot table. You can see the screen shot the subtotal is calcing 19.2% but should be closer to the 70%'s.
Hope this makes sense, if anyone can help provide any clarity it would be greatly appreciated! Thank you in advance!
My utilization measure is as follows: =sum(Data[Jan-19])/sum(EmpCalc[Jan])
I then created a Pivot which is has the following fields... Employee Type, Department, Employee Name. For each employee, the utilization measure is calculating correctly but when I subtotal by Department, the utilization percentage is way lower. It appears it is taking all the hours in the specific employee type/department but then dividing it by all standard hours in that department ignoring that employee type is in front of department in the pivot table. You can see the screen shot the subtotal is calcing 19.2% but should be closer to the 70%'s.
Hope this makes sense, if anyone can help provide any clarity it would be greatly appreciated! Thank you in advance!
