Hello Sunny!
At first glance, this seems to be exactly what I wanted!
I opened it in Excel 2016 and the selecting the slicers also changed the chart as should be.
Now I just have to figure out just exactly what you did
I guess the dummy series you used replaces the "Filler" columns in my PVT.
Thank you!
I'll keep you posted.
Steve
Sneaky trick, Sunny 😉
The only problem I see is that the primary and secondary vertical axis aren't to the same scale for Riviera school and Franklin school, so the column heights are misleading. e.g. for Riviera school if you take the sum of Jan's labor, material and overheads it comes to 795. Whereas the total revenue is only 740, yet it's column is higher than the costs column.
You'd have to manually set the axis heights, but this wouldn't be ideal for all schools.
Mynda
Hi Mynda
The total revenue column (740) is lower than the total overhead (795) on my chart.
Am I missing something?
Sunny
Interesting...when you display both vertical axes the scale for the two series changes so that the 740 column is higher than the 795 column. I turned the axis labels on so I could check they were plotting data on the same scale. I guess they change to share a common scale when only one or no axes are displayed to avoid this issue, but I've never noticed this before. Probably because I typically plot percentages on one axis and whole numbers on another, so having different scales is the primary reason for using the secondary axis. Anyhow, crisis averted, great job.
Hi Mynda
You are correct. If one of the axis is deleted it will follow the scale of the other axis (common scale).
In this case I deleted the secondary axis first so it uses the primary axis.
I then deleted the primary axis.
OK, OK, UNCLE!That is over my head!!
Mynda, do you think this is something you could show to MS that they could work out a simple solution for?
As a Microsoft MVP they would certainly listen to you and at least look into it, I am sure.
I think it is a very good way to show profit/loss comparisons.
Mynda and Sunny, have a wonderful and healthy weekend and thank you very much for your help.
I'll let you know how I am faring with your solution.
Steve
Hi Steve,
From what I know about the current priorities for building new chart functionality, I'd say the chances of Microsoft building a new PivotChart to handle this scenario is almost zero, unfortunately.
Mynda
