calculated field in pivot table.

[Tripleseas]

Hello community,

I have a data set of sales transactions for each year as shown ine the table below :
I want to calculate the variation for each product. I have created a pivot table but i can't add a calculated field for this variation because i have just one column ( number of transactions )

so i added the variation manually outside of the pivot table but that's not ideal for me. is there any solution to this please.

in the calculated field ideally i will have : = number of transaction in the year 2022 - number of transactions in the year 2021.

hope i was clear thank you

Classeur1
	A	B	C	D	E	F	G	H	I
1	year	mois	PRODUITS	Code	Agence	Number of transaction	Volume of transactions
2	2021	January	X1	1001	X22	4	8230,54
3	2022	January	X1	1001	X22	1	251,12
4	2021	January	X1	1001	X22	2	7065,95
5	2022	January	X1	1001	X22	0	0
6	2021	January	X1	1002	X22	3	5025,62
7	2022	January	X1	1002	X22	0	0
8	2021	February	X1	1002	X22	3	1522,28
9	2022	February	X1	1002	X22	0	0
10	2021	February	X2	1002	X22	2	4307,4
11	2022	February	X2	1002	X33	0	0
12	2021	February	X2	1002	X33	5	13396,13
13	2022	February	X2	1003	X33	0	0
14	2021	February	X2	1003	X33	6	18784,29
15	2022	February	X2	1003	X33	0	0
16	2021	February	X2	1003	X33	4	5486,89
17
18
19
20				year	Valeurs
21				2021		2022
22		Code	Agence	Somme de Number of transaction	Somme de Volume of transactions	Somme de Number of transaction	Somme de Volume of transactions	Variation Number	Variation volume
23		1001	X22	6	15296,49	1	251,12	-5	-15045,37
24		1002	X22	8	10855,3	0	0	-8	-10855,3
25			X33	5	13396,13	0	0	-5	-13396,13
26		1003	X33	10	24271,18	0	0	-10	-24271,18
Feuil1

Cell Formulas
Range	Formula
H23:I26	H23	=F23-D23

 

[gelu]

Hi T,

Instead of Calc Field use show Values As.

Book2
	A	B	C	D	E	F	G	H	I	J
1
2
3			year	Values
4			2021				2022
5	Code	Agence	No of transaction	Vol of transactions	Var No of Trans	Var Vol	No of transaction	Vol of transactions	Var No of Trans	Var Vol
6	1001		6	15296.49			1	251.12	-5	-15045.37
7		X22	6	15296.49			1	251.12	-5	-15045.37
8	1002		13	24251.43			0	0	-13	-24251.43
9		X22	8	10855.3			0	0	-8	-10855.3
10		X33	5	13396.13			0	0	-5	-13396.13
11	1003		10	24271.18			0	0	-10	-24271.18
12		X33	10	24271.18			0	0	-10	-24271.18
13	Grand Total		29	63819.1			1	251.12	-28	-63567.98
14
15
Sheet4

Drag Number of Transactions and Volume of Transactions twice (each) in the Values Area
1 - In value field settings for 2nd No of Transactions, write the Title (Name Var no Of Trans)
2 - Select Show Values As
3 - Select Difference From
4 - Base Field Year, Base Item 2021

Repeat for 2nd Vol of Transaction


You can hide columns E & F

 

[Tripleseas]

Hi T,

Instead of Calc Field use show Values As.

Book2
	A	B	C	D	E	F	G	H	I	J
1
2
3			year	Values
4			2021				2022
5	Code	Agence	No of transaction	Vol of transactions	Var No of Trans	Var Vol	No of transaction	Vol of transactions	Var No of Trans	Var Vol
6	1001		6	15296.49			1	251.12	-5	-15045.37
7		X22	6	15296.49			1	251.12	-5	-15045.37
8	1002		13	24251.43			0	0	-13	-24251.43
9		X22	8	10855.3			0	0	-8	-10855.3
10		X33	5	13396.13			0	0	-5	-13396.13
11	1003		10	24271.18			0	0	-10	-24271.18
12		X33	10	24271.18			0	0	-10	-24271.18
13	Grand Total		29	63819.1			1	251.12	-28	-63567.98
14
15
Sheet4

View attachment 78725



Drag Number of Transactions and Volume of Transactions twice (each) in the Values Area
1 - In value field settings for 2nd No of Transactions, write the Title (Name Var no Of Trans)
2 - Select Show Values As
3 - Select Difference From
4 - Base Field Year, Base Item 2021

View attachment 78727

Repeat for 2nd Vol of Transaction


You can hide columns E & F

All I can say is thank you so much for this amazing trick. it has worked for me.
your help is much appreciated. have a good day .

 

[Tripleseas]

Hi T,

Instead of Calc Field use show Values As.

Book2
	A	B	C	D	E	F	G	H	I	J
1
2
3			year	Values
4			2021				2022
5	Code	Agence	No of transaction	Vol of transactions	Var No of Trans	Var Vol	No of transaction	Vol of transactions	Var No of Trans	Var Vol
6	1001		6	15296.49			1	251.12	-5	-15045.37
7		X22	6	15296.49			1	251.12	-5	-15045.37
8	1002		13	24251.43			0	0	-13	-24251.43
9		X22	8	10855.3			0	0	-8	-10855.3
10		X33	5	13396.13			0	0	-5	-13396.13
11	1003		10	24271.18			0	0	-10	-24271.18
12		X33	10	24271.18			0	0	-10	-24271.18
13	Grand Total		29	63819.1			1	251.12	-28	-63567.98
14
15
Sheet4

View attachment 78725



Drag Number of Transactions and Volume of Transactions twice (each) in the Values Area
1 - In value field settings for 2nd No of Transactions, write the Title (Name Var no Of Trans)
2 - Select Show Values As
3 - Select Difference From
4 - Base Field Year, Base Item 2021

View attachment 78727

Repeat for 2nd Vol of Transaction


You can hide columns E & F

Just one quick question please : I want to sort the variation of volume from the biggest to the smallest but that dont seem to work for me. is that possible. thanks

 

[gelu]

Right click on label (Var Vol), sort. It does sort for each code.

 

[Tripleseas]

Right click on label (Var Vol), sort. It does sort for each code.

hello again,
i want to thank you for the method you showed me before i'm working with it just fine. However, if you may let me pose this question :
I want to sort the variation volume field from smallest to biggest but i just can't seem to get it to work.
when i sort it just sort the volume column and not the show value as field. and this is so important to me because in matter of fact i'm hiding all other columns just the variation that is being shown. and people using the pivot table need to see the variation from smallest to biggest. i tried to explain below :

variation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		Année	Valeurs
4		2022				2023
5	Code	Number	Volume	Variation Volume	Variation Number	Number	Volume	Variation Volume	Variation Number		RESULT WANTED
6	1010	2,604	5,757,086			0	0	-2,604	-5,757,086		-5,757,086
7	9999	7,148	7,687,893			8,639	9,179,029	1,491	1,491,137		-2,239,470
8	9002	9,556	8,355,966			7,935	6,116,496	-1,621	-2,239,470		1,491,137
9	9000	18,775	129,516,443			36,132	173,285,058	17,357	43,768,615		43,768,615
10	9900	0	0			60,633	131,437,289	60,633	131,437,289		131,437,289
11
12
13
14
15
Feuil2

thank you for your help