Stacked Column Pivot Chart Plot Grand Total as a Series

[MartinSmith]

Hi all

Is it possible to create a Pivot Chart like this? The line is the total of the other series.

I can create this by inserting a normal chart then choosing the total series as Stacked Line with Markers.

Using a Pivot Chart you don't seem to be able to add the Grand Totals to the Pivot Chart in any way. I would then be able to format the series as shown below.

The important thing is automating the update process.

[/URL][/IMG]

 

[Steve=True]

Hi Martin,

Yes, it can be done but will take a little work on the part of Excel trickery.

1) Add a Cummulative column of data to your pivot table =SUMIF([[Region]],[Region],[[Sales]]) - where region is your 1,2,3 and the Sales are the Values in your chart. Note that you will have to put the first region and sales in double brackets if your data is in a table so that it uses the whole range to sum.
2) Create your pivot table and add the new cumulative column of data to it in the Values section of the pivot table with a sum of the data.
3) Create pivot chart
4) You will now have 4 more series on your pivot chart. Select each new series and "Change Chart Type" to a line chart.
5) Select 3 of the 4 legend value that are the same for your new line and delete them.

Hope this helps.

Steve=True

 

[Steve=True]

Hi Martin,

Here is a quick tutorial and sample file in case you need more help.

how-to-add-a-grand-total-line-on-an-excel-stacked-column-pivot-chart

Steve=True

 

[RoryA]

I'm not really sure why you need to add a line, when the column heights already show that information, but you could add either a calculated field or calculated item (depending on your data layout) to the table.

 

[MartinSmith]

Hi Rory

Yh I asked the same question, but that's what 'they' want! I tried the calculated field but the data layout would permit me to use it.

Cheers

 

[MartinSmith]

Thanks Steve=True

I'll give these a go today.

Many thanks for your response.

Martin

 

[eweynand]

This video shows how to add the grand total line while maintaining the dynamic nature of the pivot chart. https://youtu.be/j3aC_Wik2SM

What do you think?