Pivot Chart - Issues with secondary axis in reverse order

[Jacob Petersen]

Hi,

I have a pivot chart with multiple slicers for convenience and user-friendliness.
However, when i select two options in the slicer: Price and volume, it is displayed correctly on the graph with two different line charts. When i try to move the Price graph to the secondary axis, the order of the data is reversed in the pivot chart and the data is not matching the dates when displayed on the chart.

Have anyone had this problem before and any possible solutions?

Please see the attached example.
I am using Excel 2010

Thank you in advance.

Best regards
Jacob

 

[Chihiro]

It's because of the way your data is organized.

See the sample table created below your Data. Sheet1 shows new PivotChart based on that table.

 

[Jacob Petersen]

Hi Chihiro,
Thank you for your reply!
Is this the only solution that will fix my problem?
In my dataset I currently have 16000 rows and with 79 unique date periods it will probably be too extensive since each value will also have to be displayed for 4 different sectors and 6 unique measures.
Will approximately need 1.4 Million rows to reorganize my data.

 

[Chihiro]

I'd recommend one of the following in that case.

1) Load it on Database build View and connect to Excel to generate report

2) Split data into multiple workbook, say 1 for each year. Use DataConnection and PowerPivot to summarize data and report

3) Use Advanced Filter etc to extract data for segment you wish to report/present. Build your report based on that secondary data set.

4) Summarize data to Monthly, Quarterly or Yearly level and report. When you need drill down, use one of above method to extract only the segment needed.

In most cases, you are either looking at macro trend over years, or looking at more granular data over single year, quarter etc.

 

[Jacob Petersen]

Thank you once again Chihiro. I will definitely try to look into that.