Adding additional data to pivot charts

[keith2511]

I have several pivot bar charts, all derived from the same dataset.

The data relates to locations which belong to a region and sub-region.

I want to be able to show a regional average line in each chart and also a sub-regional average line, when the charts are filtered by a slicer.

How can I achieve that?

Thanks in advance.

Keith

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[keith2511]

Workbook uploaded, as requested.

[MrShorty]

Excel's pivot charts are very good at some things, and rather limited at other things. They are very dynamic, and can easily respond to changes to the associated pivot table. They are limited in that they must display all of the data in the pivot table and cannot display data from outside of the pivot table. I see two possible approaches:

1) Edit the pivot table/slicer so that the region/sub-region average is shown in the pivot table.
2) Compute the region/sub-region averages outside of the pivot table and build a regular chart based on the pivot data and the computed averages.

I do not have the skills in Excel's pivot tables to know how best to do 1. Assuming you know how to get the sub-region averages (by formula or another pivot table), making a regular chart from pivot data is described in this tutorial: https://peltiertech.com/regular-char...-pivot-tables/

How do you want to proceed? What help do you need from us to implement that choice?