How to calculate employee utilization % which is shown over 3 month period?

[Oksana]

Hello,

I am new to Power BI world and need to create a relatively simple report for our HR team.

They asked to show average employee utilization as a % which is split by level and gender.

I have the following columns from my Timesheets table which i need to include in order to calculate utilization:

ProductiveTime

FixedWorkingHours

Training Time

Sickness

Absence

It is easy to calculate utilizaiton by dividing productive time over fixed working hours,;however, could you please help me create a measure which subtracts correctly non-productive time, i.e. training time, sickness and absence, so that i could have an actual and correct utilization rate.

Once i have utilization as a %, i also need to show it as an average utilization by gender over 3 months. What would be the formaulat for that?

Many thanks for your help and advice

Oksana

[kentyler · Accepted solution]

Have you considered unpoviting your data ?

So you have a table with fields:

Employee

Time Type

Hours

If you then use Power Query to convert training, sickness and absence to negative numbers

Then you can get productive time by adding them up with fixed working hours. That would give you a number you could compare with the reported productive time numbers.

[kentyler · Accepted solution]

To do caclulations by gender you would need to have an employee dimension table, which had a relationship to your table of hours. That would let you filter the hours table by gender easily in order to calculate by gender.

[kentyler · Accepted solution]

Have you considered unpoviting your data ?

So you have a table with fields:

Employee

Time Type

Hours

If you then use Power Query to convert training, sickness and absence to negative numbers

Then you can get productive time by adding them up with fixed working hours. That would give you a number you could compare with the reported productive time numbers.

[Oksana]

Thank you @kentyler   your solution is great but I created a measure and it works.