Forum Discussion

ezequiel's avatar
ezequiel
Microsoft Employee
7 years ago
Solved

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 tr...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

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

    =AVERAGE(Tracker[Hours])

    Hope this helps.

MFelix's avatar
MFelix
Super User
7 years ago

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

ezequiel's avatar
ezequiel
Microsoft Employee
7 years ago

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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

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

    =AVERAGE(Tracker[Hours])

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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...  

    • MFelix's avatar
      MFelix
      Super User
      6 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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")