Hello,

I have a series of data that is pivoted by quarter against sales type, which I have then turned into a connected PivotChart. I have set the sales types as line charts, and the grand total is set as a bar chart with a secondary vertical axis. (I have manually added the Grand Total to the data series as a new field, otherwise I know the Excel 2013 auto-pivot Grand Total cannot be included in PivotCharts).

I then have a filter to look at this information by customer. The problems I'm facing are:

 - When I filter by customer, and then even when I filter back to 'All', the chart formatting seems to reset to one axis and all of both the sales types and the grand total being shown as bar charts.

- Furthermore, as some customers have 2 sales types, and some will have all 4, the colour of the sales type bars/lines change between customer.

Is there a way to set the chart to consistently show the same 5 areas (4 sales types and a grand total) in the same chart type (combo of 4 line graphs and one bar chart) where it retains the secondary axis and the colour of the 5 lines/bar remain the same regardless of how many of the 5 categories have a value >0?

Many thanks,

Mike