This is a question that I'm pretty sure I know that answer to, and that is that it can't be done. However, I thought it worth asking the question to see what suggestions there may be to achieve what I am trying to do.
I am trying to make a pivot table that will then be the basis for a pivot chart. The chart is to be a line chart that compares the number of widgets installed to the revenue and expenditure, over a number of months, with all data being cumulative, that is, in pivot table terms, I'm showing the values as a running total by month.
The problem is that the revenue and expenditure is from the same data source and they are differentiated by the account type, being revenue or expenditure (there are also balance sheet accounts but they aren't relevant). So I can filter for one or the other using a slicer or filter but it affects both.
Is there a way to do this? I tried creating two pivot tables but I don't think a pivot chart can incorporate data from two pivot tables.
Hi Mardi,
You're right, you can't create a Pivot Chart from multiple PivotTables, so you either have to fix your source data so it works the way you want, or you can create a regular chart from multiple PivotTables, as described here.
Mynda
Brilliant, thanks! I had originally done it by making a table from various pivot tables' data but the table couldn't share slicers so all that was getting unwieldy. This is a much better solution 🙂
