Forum Discussion
Calculating Employee Productivity
Dear community members,
Need your expert help in the following situation.
I have three tables: Hours, Employee, and Project.
The Hours table contains the hours details by each employee for a week:
Each employee has a weekly base hours:
Model:
The objective is to caclulate the Utilization% for multiple dimensions (by employee, by department, etc.), and analyze the trend
The formula for utilization% is as follows: DIVIDE([Productive Hours]/[Effective Base Hours])
Where Productive Hours = SUM('Employee Hours'[Hours]) excluding certain Tasks (Say Task 3, 19 & 44)
and Effective Base Hours = SUM(Employee [Base Hours]) excluding certain Tasks (Say Task 3, 19 & 44)
In case of any mid-week joining/separation, the Base hours should be pro-rated accordingly.
Any support/guidance will be appreciated.
Thanks,
vir
1 Reply
Hi Anonymous ,
use these measures:
Productive Hours = CALCULATE(SUM('Employee Hours'[Hours]), FILTER(Table_name, Task <> "Task 3" || Task <> "Task 19" || Task <> "Task 44"))Effective Base Hours =CALCULATE(SUM(Employee [Base Hours]), FILTER(Table_name, Task <> "Task 3" || Task <> "Task 19" || Task <> "Task 44"))
Utilization %= DIVIDE([Productive Hours],[Effective Base Hours]) .....format this to percentage.
I hope this helps!
Mark this as a solution if I answered your question. Kudos are always appreciated!
Thanks.
