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.