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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Macaurly
Regular Visitor

Calculating an average of hours worked for different 17-week periods

Hi all, 

I need to make a report which shows how many employees in a company worked an average of 47 hours per week in a given 17-week period.

 

I have currently managed to group my date column outside of Power Query into multiple 17-week periods (as in the first image), and would like to use this group in PowerQuery to then group by this colum (the button shown in the second image) so that my table shows all hours worked in this 17-week period. I could then calculate an average, however from what I can tell, I am unable to use this group in Power Query. Therefore, I am looking for an alternative to my problem. The third image is my table of data so far.

ThankPBI - Groups of dates.PNGPBI - group by button.PNGPBI - PQ List.PNG

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @Macaurly,

 

In your scenario, you can create a measure based on the group like below: 

 

Measure = AVERAGEX(SUMMARIZE('Table1','Table1'[TimesheetDate (groups)],"Total",SUM('Table1'[TotalHours])),[Total])

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
rbalanci
New Member

HI! kindly anyone can support me on how to convert the average hours correctly as it's exceeding 60 minutes in bi and not converting to hours like example, 96 minutes should be 1:36.

average hrs.png

v-qiuyu-msft
Community Support
Community Support

Hi @Macaurly,

 

In your scenario, you can create a measure based on the group like below: 

 

Measure = AVERAGEX(SUMMARIZE('Table1','Table1'[TimesheetDate (groups)],"Total",SUM('Table1'[TotalHours])),[Total])

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi! Can you please support need your support how i can get correct average hours of below in power BI. example for instead of 1.99 minutes it  should apear as 2.39 or 2 hour and 39 minutes. i tried so many formula but not converting to hours after exceeding 60 minutes
.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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