cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Macaurly Frequent Visitor
Frequent 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 Felix


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

Proud to be a Datanaut!




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 Felix


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

Proud to be a Datanaut!




View solution in original post

Macaurly Frequent Visitor
Frequent Visitor

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

Thanks Felix, this is great.

Macaurly Frequent Visitor
Frequent 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 Felix


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

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors