Average hours worked split by job title

[Rodney2022]

Hi

Hoping someone can help!

We need some help with a calculation in Power BI to show average hours worked per day for each job title. For example,

Date range: 1 December - 31 Demcember

Job title	Worked hours (sum)	Average hours per day
Level 1	100	?
Level 2	150	?
Level 3	200	?
Level 4	150	?

Is it possible to put in a formula that counts the number of people at each level and then works out the average hours per day in a set time frame (that updates when we amend the date slicer)?

Please let me know if you need any further information and many thanks in advance for your help!

[v-kkf-msft · Accepted solution]

Hi @Rodney2022 ,

You need to have a Calendar table and mark in the table whether it is a weekday or not, like this:

Then try the following measure.

Measure = 
DIVIDE( 
    SUM('Table'[Worked hours]),
    COUNTROWS( FILTER( 'Calendar', 'Calendar'[IsWeekday] = TRUE() ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @Rodney2022 

Does your report have a Date Table? Can you share a sample of your data in a text format?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Rodney2022]

To be able to add a table to our dashboard that shows worked hours per day by level/job title for the selected time period.

Fo example - from 1 December - 31 December, level 1's worked an average of 6.5 hours per day (based on working days in that month).

[smpa01]

@Rodney2022  from 2021-12-01 - 2021-12-31 the number of working days are 22 (

Excluded 4 Saturdays

Excluded 4 Sundays).

 

So the result should 100/22=4.5.

 

How are you getting 6.5?

[smpa01]

@Rodney2022  what is the expected ouput for the given data?