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
ezequiel
Employee
Employee

Average hours per week

Hi

 

I'm trying to show the average hours per week per employee. The table contains Employee Name, hours, Project Name and week_number. One employee can have multiple projects in one week.

 

I tried the following:

 

Avg_hours = CALCULATE(AVERAGE(Tracker[Hours]), ALLEXCEPT(Tracker, Tracker[Employee_name)], Tracker[WeekNum]))
 
But this is returning the same number regardless the week selected. 
 
Am I doing something wrong?
 
Thanks,
 
Ezequiel

 

 

 

1 ACCEPTED SOLUTION

Hi,

Drag Employee Name and Start of week to the Table visual and write this measure

=AVERAGE(Tracker[Hours])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

Hi @ezequiel ,

 

Can you share some more details on the information and data, share an example of the file and expected result?

 

I made a simple PBIX file and I'm getting changes on a card based on a slicer of the week but this can be different from your results.

 

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

 

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 @MFelix 

 

As example an employee has this data

 

EmployeeNameHoursStart of WeekWeekNum
John77/29/20191
John77/29/20191
John78/5/20192
John78/5/20192
John218/5/20192
John28/12/20193
John58/12/20193
John48/12/20193
John28/12/20193
John88/12/20193
John98/12/20193
John28/12/20193
John88/12/20193

 

Using the formula  Avg_hours = CALCULATE(AVERAGE(Tracker[Hours]), ALLEXCEPT(Tracker, Tracker[EmployeeName], Tracker[WeekNum]))

 
The result I'm getting is 6.79 for the week of 7/29, 8/5, 8/12. The expected result would be
 
Week of 7/29: 7 hours
Week of 8/5: 11.67 hours
Week of 8/12: 5 hours
 
Thanks for the help
 
Ezequiel
Anonymous
Not applicable

Hi @ezequiel 

 

Did you ever get an answer to this question? I want to do the same thing but I want to work out a 17 week rolling avg per employee...  

Hi @Anonymous ,

 

The answer is marked in the post, however this is not a rolling average is just a simple average for the week selection.

 

Can you share you data and expected result.


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



Anonymous
Not applicable

Thanks @MFelix 

I have a data table extract (sample data) and I summarized in a calculated table the total overtime per employee per week (because there are other filters as well). I need the 17week rolling average weekly OT hours and then I need to count the number of instances where the Avg 17 rolling week OT hours was > 5 hrs.

 

I can calculate the rolling average but Im struggling with doing it per employee and then the count…

 

Data table extract

     

Personnel Number

Start date

Attendance hrs

Week

Threshold Cat

WSR Shift

A

1/10/2018

3

4

Below

N

A

2/10/2018

6

4

Below

N

A

3/10/2018

0

4

Below

N

A

4/10/2018

0

5

Below

N

A

5/10/2018

5

5

Below

N

B

1/10/2018

10

4

Below

N

B

2/10/2018

6

4

Below

N

B

3/10/2018

3

4

Below

N

B

4/10/2018

1

5

Below

N

B

5/10/2018

2

5

Below

N

 

 

Calculated table

   

Employee Number

Start date

Total 5 HR

Week

A

1/10/2018

9

4

A

4/10/2018

5

5

B

1/10/2018

19

4

B

4/10/2018

3

5

 

 

Calculated table formula                                                                        

WeeklyOTTbl =                                                             

SUMMARIZE (                                                               

'OT Combined',                                                             

OT Combined'[Personnel Number],                                                                     

'OT Combined'[Week],                                                                                                                                          

"Total 5 HR", CALCULATE(SUM ( 'OT Combined'[Attendance hours] ),'OT Combined'[Threshold Cat] = "BELOW",'OT Combined'[WSR Shift] = "S")                                            

Hi @Anonymous ,

 

you don't need to do a calculated table you just need to place the values on your visualizations and make the filtering correctly.

 

However if you need to make a calculated table the formula would be:

WeeklyOTTbl =
SUMMARIZE (
    FILTER (
        'OT Combined';
        'OT Combined'[Threshold Cat] = "Below"
            && 'OT Combined'[WSR Shift] = "N"
    );
    'OT Combined'[Personnel Number];
    'OT Combined'[Week];
    "Start Date"; MIN ( 'OT Combined'[Start date] );
    "Total 5 Hours"; SUM ( 'OT Combined'[Attendance hrs] )
)

Be aware that I'm filtring the Shift with N because is the value you have on your data you should adjust it to your desired data.

 

Check PBIX file attach with the calculated table and a table visualization without any calculated values.

 

 


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,

Drag Employee Name and Start of week to the Table visual and write this measure

=AVERAGE(Tracker[Hours])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.