Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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
1 ACCEPTED SOLUTION
Community Support

Hi @Icebear ,

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

5 REPLIES 5
Community Support

Hi @Icebear ,

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

Frequent Visitor

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

Community Support

Hi @Icebear ,

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

New Member

this worked. thank you so much

Frequent Visitor

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors