Pivot Table Averaging based on COUNT of items selected with slicer

[mrpaw]

I have a 22 year data table with about 100 columns of gathered info. I can create all the pivot tables and pivot charts I want with an average based on the total count of years. Great. What I need to do is to create an average of the total based on the count of slicer items selected. Example: Row: Years 1995 - 2016. Column: Total widgets produced. Total Sum for period is 4,685 (bar pivot chart). Total Average is 213 (line in same pivot chart). All is good. But when I add a slicer and the user selects, let's say, 3 years, my averaging formula still shows the 22 year avg. How do I calculate the average of the total widgets for the years selected?

Years selected 1995 - 254 widgets, 2005 - 248 widgets, 2015 - 313 widgets. The line should move from 213 to the new average of 272.

 

[Oeldere]

Did you refresh the data?

 

[mrpaw]

Thank you, yes. But I figured out how to achieve my goal. I neglected to say that in the original data table their are monthly amounts totaled to created the annual totals. I added a helper column in the data table for the annual totals and now all is well. I was trying to avoid adding another column to my data table but I have at least satisfied my goal.

Thank you again.

 

[mrpaw]

Thank you, yes. But I figured out how to achieve my goal. I neglected to say that in the original data table their are monthly amounts totaled to created the annual totals. I added a helper column in the data table for the annual totals and now all is well. I was trying to avoid adding another column to my data table but I have at least satisfied my goal.

Thank you again.

I spoke too soon. Still can't get this to work.

 

[mrpaw]

OK - figured it out. What a lesson. Googled again and found a You Tube video that put me on the right path. What you have to do is create the original pivot table. Copy and paste everything including the Grand Total line as a link into another worksheet adding any additional formulas (this way any changes to the original source will carry through). In my case it was sum the total and divide by the count. Insert a new pivot table and create your pivot chart. I'm so happy.