google sheets - How to add a calculated column to pivot table

Question

I try to include a column that is calculated on a basis of a % difference between values of 2 columns in a pivot table. I tried calculated field but could not figure out how to describe these columns. These are year values based on one column in dataset so I could not use just column name.

I've prepared a playground with limited data and desired result that I can make using standard formulas. But I need it to be a part of Pivot Table as this is much bigger project and includes expanding rows and changing dimensions.

Here is how it looks like (K is a column that calculates % difference between G and H columns) and I need to include this one into Pivot.

My file is here: https://docs.google.com/spreadsheets/d/1yTDExBSvRXXO3P71CH09oerFp2Is7UXqzeuwNlE5XbM/edit#gid=0

Answer 1

try:

={{QUERY({A3:C}, "select Col1,sum(Col2) where Col1 is not null group by Col1 pivot Col3", 1), 
 QUERY(QUERY(QUERY({A3:C}, "select sum(Col2) where Col1 is not null group by Col1 pivot Col3", 1), 
 "select (Col2-Col1)/Col1 label (Col2-Col1)/Col1'Calculated column' format (Col2-Col1)/Col1 '0.00%'"))};
 {"Grand Total", INDEX(QUERY({A3:C}, "select sum(Col2) where Col1 is not null pivot Col3", 1), 2), ""}}