cancel
Showing results for 
Search instead for 
Did you mean: 
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.

Thanks

PBI - Groups of dates.PNG

PBI - group by button.PNG

PBI - PQ List.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

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

Hi @Macaurly,

 

First of all you are making the groups in PBI desktop part and this is not usable on the power query part, this are two separate parts on PBI you have the Query part that allows you to make a treatemtn of your data making calculation, additional columns, groupings, ..., (this works in M language) in order to have your information ready to make charts, and the "front office" on PBI where you have measures, columns, groupings, visuals,... (this as DAX language where needed).

 

What you need to do is add you groups to a visual (table or graph) and then just add the TotalHours and do the usm or average and you got what you need.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

4 REPLIES 4
Super User III
Super User III

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

Hi @Macaurly,

 

First of all you are making the groups in PBI desktop part and this is not usable on the power query part, this are two separate parts on PBI you have the Query part that allows you to make a treatemtn of your data making calculation, additional columns, groupings, ..., (this works in M language) in order to have your information ready to make charts, and the "front office" on PBI where you have measures, columns, groupings, visuals,... (this as DAX language where needed).

 

What you need to do is add you groups to a visual (table or graph) and then just add the TotalHours and do the usm or average and you got what you need.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

Highlighted
Macaurly
Regular Visitor

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

Thanks Felix, this is great.

Macaurly
Regular Visitor

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

 Hi again @MFelix

Thank you for your suggestion as it did help, and I have managed to show the employees who have worked over 47 hours in a period as a multi-row card (seen in the first image of this reply). However, it seems I can't use the average of total hours (or the equivalent measure I calculated as seen in the second image of this reply) in the legend field for a pie chart. My goal is to be able to see how many employees worked each of the average hour values in the selected time period. For example, in the pie chart (seen in the third image of this reply), I currently have each of the total hours values on the legend which isn't useful to show. I would like this to instead show each of the average values visually. 

I hope this is clear enough and thanks again in advance. 

PBI - card and slicer.PNG

PBI - average hours.PNG

PBI - pie.PNG

Super User III
Super User III

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

How are you calculating the average?

Mfelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors