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

[CEOMisza]

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]

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:

Book1
	A	B	C
1	Product	2021	2022
2	ad hoc crush	7036	8259
3	fluttering marble	4661	2710
4	pine green group	1688	5707
Sheet5

I got this Pivot Table

Book1 (version 2).xlsb
	E	F	G	H
3	Row Labels	% of 2021 Total	% of 2022 Total	Y/Y % Change
4	ad hoc crush	8.78%	7.51%	8.81%
5	apparel	9.09%	8.76%	22.54%
6	auburn join	10.50%	10.14%	22.91%
7	cardinal balance	9.97%	13.07%	66.70%
8	carmine farmer	5.31%	7.19%	72.22%
9	celadon trade	7.59%	6.92%	15.98%
10	cinnabar copper	14.49%	12.05%	5.80%
11	cloistered cart	13.11%	6.51%	-36.85%
12	fluttering marble	6.89%	7.28%	34.33%
13	old lavender basket	6.32%	9.00%	81.08%
14	pine green group	7.97%	11.57%	84.73%
15	Grand Total	100.00%	100.00%	27.17%
Sheet5

In the Pivot Table, the values shown are % of Grand Total.
And confirmed with formulas:

Book1
	E	F	G	H	I
18	ad hoc crush	13,218	14,382	1,164	8.81%
19	apparel	13,683	16,767	3,084	22.54%
20	auburn join	15,804	19,424	3,620	22.91%
21	cardinal balance	15,011	25,023	10,012	66.70%
22	carmine farmer	7,995	13,769	5,774	72.22%
23	celadon trade	11,422	13,247	1,825	15.98%
24	cinnabar copper	21,810	23,075	1,265	5.80%
25	cloistered cart	19,733	12,461	7,272	-36.85%
26	fluttering marble	10,378	13,941	3,563	34.33%
27	old lavender basket	9,512	17,224	7,712	81.08%
28	pine green group	11,992	22,153	10,161	84.73%
Sheet5

Cell Formulas
Range	Formula
E18:E28	E18	=SORT(UNIQUE(Table1[Product]))
F18:F28	F18	=SUMIFS(Table1[2021],Table1[Product],E18#)
G18:G28	G18	=SUMIFS(Table1[2022],Table1[Product],E18#)
H18:H28	H18	=G18#-F18#
I18:I28	I18	=H18#/F18#
Dynamic array formulas.

Hope that helps.