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

[ChelseaCL]

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 

[#2]

@ChelseaCL 

Google doesn't give access to the file, could you share it as Excel file by another way?

[#3]

@Sergei Baklan 

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.

[#4 · Accepted solution]

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

[#5]

@Sergei Baklan 

Thanks so much, you're a lifesaver!  This explains a lot. xD

I'm running into one more problem.  I formatted my numbers as pictured below to make it so that the percentage sign shows, but whenever I choose a different value to filter by, my labels lose their formatting.  This was happening too with their color, glow, and any other changes I made to them.  Am I not doing this correctly?  Aren't I supposed to edit the color, formatting, etc. in this side box?  Or is there somewhere I'm supposed to edit the base "template" somehow?