Forum Discussion

Vicki Ours's avatar
Vicki Ours
Copper Contributor
Jul 24, 2017

Averages in a Pivot Table

I created a pivot table to summarize all activities a user performs each day.  The source worksheet has multiple rows by user name and the date they worked (user rows are not contiguous).

 

I now need to show an average daily (and preferably weekly) productivity for EACH individual user based on only one of the pivot table fields ("Processed") and the number of days that user worked. 

 

For example, Jack completed 5 records on Monday, 7 on Tuesday, and 6 on Friday, for a total of 18 records over 3 days, with the average being 6.  How do I get the pivot table to calculate the average records processed daily/weekly for each user based on the number of days they worked (which will be different for each user)?  I inserted a calculated field and selected the Average option, but the result is completely incorrect.

 

I am familiar/comfortable with pivot tables and formulas, but for some reason, this has me completely stumped!

 

Thank you!

Formulas & Functions
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 24, 2017

    Hi Vicki,

     

    Creating the pivot table be sure it's added to data model. You may add measure to it like

    =COUNT([Name])/DISTINCTCOUNT(Table1[Date])

    which gives the average per day in the period number of records

     

     

Resources