Calculating Employee Productivity

[Anonymous]

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.

Source File Link

Sample PBIX file

@Ashish_Mathur

@Fowmy 

@Greg_Deckler 

 Any support/guidance will be appreciated.

Thanks,

vir

[Tanushree_Kapse]

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.