How do I make a chart using two different pivot tables' grand totals

[jorgegarcia1]

Hello, 

I'm making an income v expenses spread sheet, as a part of if I've created two pivot tables (Income and expenses) I'm using the grand totals in a line chart to visualize them, but when my pivot tables change and the grand total row shifts down I lose my visuals. 

How can I lock the grand totals as my selection for each line in my graph?

Thanks

[#2 · Accepted solution]

Hi @jorgegarcia1 

use the GETPIVOTDATA function to create a little helper table and use that helper table for your chart.

Just take an empty cell somewhere outside your pivt table, start typing the = sign and click on your grand total amount in the first pivot table. This will automatically create the GETPIVOTDATA function for you:

Example:

The advantage of this function is, that at will always refer to the grand total (if you selected that on with your first step), no matter if that cell moves later up or down in your pivot table.

[#3]

Thank you! it worked great