# Pivot Table( calculated field showing diffrence between two columns values show as % of the column)



## CEOMisza (Dec 30, 2022)

Hello,
I have a simple pivot table with name of a product and sum of sales in year 2021 and 2022 shown as procent of the total column). Im trying to add a calculated field that would show the diffrence between the two years. For istance in 2021 sales of apples was 15 and grand total of sales of all products was 100, so the value displayed is 15%. In 2022 the sales of apples were 10 and grand total sales of all products was 50 so value displayed 20%. Im looking for a calculated filed that would display the diffrence between 20% and 15% and not 15 and 10. So the result im looking for would be 5% in this example.

Thank you for your help and sorry for my English, I hope u understand the issue.


----------



## jdellasala (Jan 1, 2023)

Welcome to the Forums! It is best to post actual data using *XL2BB* so that people that help have a little less work to do and can be more accurate. It's great that you have your Excel & OS Versions in your profile.
The Calculated Field formula

```
=('2022'-'2021')/'2021'
```
seems to work fine where the values (NOT percentages) of the 2021 and 2022 sales are used. From this table:
Book1ABC1Product202120222ad hoc crush703682593fluttering marble466127104pine green group16885707Sheet5
I got this Pivot Table
Book1 (version 2).xlsbEFGH3Row Labels% of 2021 Total% of 2022 TotalY/Y % Change4ad hoc crush8.78%7.51%8.81%5apparel9.09%8.76%22.54%6auburn join10.50%10.14%22.91%7cardinal balance9.97%13.07%66.70%8carmine farmer5.31%7.19%72.22%9celadon trade7.59%6.92%15.98%10cinnabar copper14.49%12.05%5.80%11cloistered cart13.11%6.51%-36.85%12fluttering marble6.89%7.28%34.33%13old lavender basket6.32%9.00%81.08%14pine green group7.97%11.57%84.73%15Grand Total100.00%100.00%27.17%Sheet5
In the Pivot Table, the values shown are % of Grand Total.
And confirmed with formulas:
Book1EFGHI18ad hoc crush13,21814,3821,1648.81%19apparel13,68316,7673,08422.54%20auburn join15,80419,4243,62022.91%21cardinal balance15,01125,02310,01266.70%22carmine farmer7,99513,7695,77472.22%23celadon trade11,42213,2471,82515.98%24cinnabar copper21,81023,0751,2655.80%25cloistered cart19,73312,4617,272-36.85%26fluttering marble10,37813,9413,56334.33%27old lavender basket9,51217,2247,71281.08%28pine green group11,99222,15310,16184.73%Sheet5Cell FormulasRangeFormulaE18:E28E18=SORT(UNIQUE(Table1[Product]))F18:F28F18=SUMIFS(Table1[2021],Table1[Product],E18#)G18:G28G18=SUMIFS(Table1[2022],Table1[Product],E18#)H18:H28H18=G18#-F18#I18:I28I18=H18#/F18#Dynamic array formulas.
Hope that helps.


----------

