Forum Discussion

ChelseaCL's avatar
ChelseaCL
Copper Contributor
Jan 07, 2022

Showing Average Percentage per Month Over Time in Pivot Table/Chart

Hi Friends!
 
Thanks for taking a look at this.  I'm pretty new to this, so feel free to link things or point something out if I've done something weird. xD
 
We have survey data which indicates a percentage score, and we'd like to average all the percentage scores for the month to give us an overall rating for the month, then chart that on a line over time.  I'm not sure how to make the pivot table average all of the values for the month, though, to give me a single percentage line in my chart.  Could you assist?  Or should I be using a non-pivot chart to show this?  I liked the pivot table since it had filters I could use in a dashboard.
 
Thanks!

 

  • Device and OS platform, PC/Windows 10
  • Excel product name and version number- Microsoft 365

Uploaded sample to Google Drive 

 

Charting
Excel
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 07, 2022

    ChelseaCL 

    Now it's available, thank you.

     

    Number in your cells were texts, even if now General format is applied to such cells. Freen triangle means

    In PivotTable you may only count texts. Thus you need numbers. For that to re-enter  values; or copy any empty cell, select cells in question, Paste, Paste Special, Add, Ok.

    With that you may aggregate any numeric field as average or whichever aggregation you need.

     

    • ChelseaCL's avatar
      ChelseaCL
      Copper Contributor
      Jan 07, 2022

      SergeiBaklan 

       

      Hi, sorry about that!  I tried OneDrive instead.  Does that work?  I tried uploading the Excel sheet directly to this forum, but I got a message saying the file type was unsupported, which seems strange.  I was working in the "WIP.User Satisfaction Survey OT" tab, by the way, trying to use the "Overall Rating %" field to get the percentages of each survey.

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jan 07, 2022

        ChelseaCL 

        Now it's available, thank you.

         

        Number in your cells were texts, even if now General format is applied to such cells. Freen triangle means

        In PivotTable you may only count texts. Thus you need numbers. For that to re-enter  values; or copy any empty cell, select cells in question, Paste, Paste Special, Add, Ok.

        With that you may aggregate any numeric field as average or whichever aggregation you need.

         

Resources