How to show utilisation of hours logged compared to workload

[FJH2024]

Hi all,

I hope everyone is well 🙂 

I've been asked to alter a PBI report I've created for my manager to help display employee hours logged against their contracted hours. So I have one table that shows all time logged against their timesheets, and another table that displays their contracted hours per week. I'm struggling to display this effectively so I'm hoping someone might have an idea!

I'm thinking that if a date slicer is applied to show what an employee was up to for the first week of Jan for example (01/01/2024-05/01/2024), I'd like a card to show a percentage that displays their utilisation for that period. So if they're contracted 40 hours in the week but they did some overtime this would be compared against their workload and show a percentage above 100%.

I really hope this makes sense, please let me know if I can provide any extra information or an example of my data to assist with any theories.

Thank you so much in advance!

[v-fenling-msft · Accepted solution]

Thanks for lbendlin's concern about this issue.

Hi, @FJH2024 

I am glad to help you.

Since you didn't give me some data to conduct the test, I assumed some data for the test myself:

Timesheet:

ContractHours:

Then create three Measure for calculating the total time recorded by the employee, the total time of the contract, and the utilization of the working time:

Total Logged Hours = SUM(Timesheet[LoggedHours])

Total Contracted Hours = SUM(ContractHours[ContractedHours])

Utilization Percentage = DIVIDE([Total Logged Hours], [Total Contracted Hours], 0)

You will then need to use Slicer visual and drag Date into a field in Slicer to filter for time:

You can then use the line chart visual to see how the working time utilization has changed for each day of the week:

You can also add a Card visual to view the working time utilization for a particular day or the whole week separately:

I've attached the pbix file for this little example below, hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FJH2024]

Hi Fen,

Thank you so much for your help on this! I've implimented it into my dashboard and will send it to my manager for his approval 🙂

Thank you again for taking the time to assist me on this, I really appreciate it!

[lbendlin]

I would use overlayed floating bars (similar to a bullet chart) to show the ratio.

Which insights are you expecting to get from the report?