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.

Thanks

PBI - Groups of dates.PNG

PBI - group by button.PNG

PBI - PQ List.PNG

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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 Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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 Super User!

Check out my blog: Power BI em Português



 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

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 Super User!

Check out my blog: Power BI em Português



Thanks Felix, this is great.

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.