Forum Discussion

ezequiel's avatar
ezequiel
Icon for Microsoft Employee rankMicrosoft 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.

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
Icon for Super User rankSuper 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")                                            

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      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.