Average hours per day among staff (multiple lines per day per person)

[Anonymous]

I have searched the forum and googled but haven't been able to implement those findings to my scenario.

• I have one table: TimeEntry
• This table contains data for 4 years, approx 200 thousand lines.
• Some persons are working full work week, while others work fewer days.
  • Each person registers many lines each day.
  • This is why I need to somehow get the sum of hours per member.id as well as finding out how many days are behind those hours using date in timeEnd column.
• I need to create a measure that tells me the average hours per day in the workforce.

Hope someone can assist me.

member.id	timeEnd	hoursBilled
1	13.4.2022 08:30:00	0,5
2	13.4.2022 10:30:00	2,5
3	13.4.2022 12:30:00	4,5
1	13.4.2022 15:30:00	5,0
1	14.4.2022 12:45:00	4,0
3	14.4.2022 12:30:00	3,5
2	15.4.2022 09:00:00	0,5
2	15.4.2022 12:30:00	3,0
1	15.4.2022 12:30:00	4,5

Added for further explanation

If we use the little table I provided:

• Person1 works 4,67 hours a day on ave
• Person2 works 3 hours a day on ave
• Person3 works 4 hours a day on ave
• Total average a day in the workforce is therefore 3,89 hours

[v-cgao-msft]

Hi @Anonymous ,

Please try this measure.

AVG = 
VAR _total = SUM('Table'[hoursBilled])
VAR _count = COUNTROWS(CALENDAR(MIN('Table'[timeEnd]),MAX('Table'[timeEnd])))
VAR _avg = DIVIDE(_total,_count)
RETURN
_avg

result.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Thank you for the reply Gao but this doesn't give the correct result.

If we use the little table I provided:

• Person1 works 4,67 hours a day on ave
• Person2 works 3 hours a day on ave
• Person3 works 4 hours a day on ave
• Total average a day in the workforce is therefore 3,89 hours

When I use your measure I don't get 3,89 as a result.

Hope you have time to look at this again.

Many thanks 

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

First we need a date column, which you can create in PowerQuery.

then please create a new measure.

AVG = 
VAR _count_id =
    DISTINCTCOUNT ( 'Table'[member.id] )
VAR _avg_day_total =
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[member.id],
            "avg", DIVIDE ( SUM ( 'Table'[hoursBilled] ), DISTINCTCOUNT ( 'Table'[Date] ) )
        ),
        [avg]
    )
VAR _avg_day_person =
    DIVIDE ( _avg_day_total, _count_id )
RETURN
    _avg_day_person

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[mychau]

this worked. thank you so much

[Anonymous]

Thank you so much you are saving my week (at least) - this is perfect!