can't sort pivot table column

[Tripleseas]

Hello,
i have created a pivot table from a data set. then I added a variation between the year 2022 and 2023 as shown below.
In my report, i want to sort the variation ( number) from the biggest negative to the biggest positive.
but when i use sorting, it just sort the volume column and not the variation column. i just can't seem to get it work.
is there any solution to this please. thank you

variation.xlsx
	A	B	C	D	E	F	G	H	I	J	K
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
Feuil2

 

[Tripleseas]

hello community is there any solution to this please. thank you

 

[Alex Blakenburg]

What happens if you click on any of the "values" in Column I eg I6. right click and select Sort then Smallest to Largest ?

 

[Tripleseas]

What happens if you click on any of the "values" in Column I eg I6. right click and select Sort then Smallest to Largest ?

Hey,
when i sort the variation column ( column I) from smallest to biggest , what is sorted is the column G ( volume) and not the variation column.

 

[Alex Blakenburg]

I think you are clicking either the column or the heading, click on 1 of the numbers in the column

 

[Tripleseas]

I think you are clicking either the column or the heading, click on 1 of the numbers in the column

That's what I did I clicked on the number inside the column but the same result.

 

[Alex Blakenburg]

OK I see what you mean.

If you are happy for the output to look a little different to what you currently have and are ok to hard code the years then you can use Calculated Items to sort by the difference.

• Click on any of the Year fields in the heading ie Année, 2022 or 2023
• in PivotTable Analyze > Fields, Items & Sets > Calculated Item
Create an item using Année by clicking on 2023 then minus then 2022 to give you ='2023'-'2022'
Give it a name. The name will show as if it was a Year in your pivot heading.
• You will now have an additional "Year" called in my case Difference in your pivot table.
If you click in that column and apply a sort it should give the desired result.

Output

20230407 PIvot Table Sort Diff Field Tripleseas.xlsm
	H	I	J	K	L	M	N	O	P
37		Année	Values
38		2022		2023		Difference		Total Number	Total Volume
39	Code	Number	Volume	Number	Volume	Number	Volume
40	1010	2604	5,757,086.28	0		-2604	( 5,757,086.28)	0
41	9002	9556	8,355,966.28	7935	6,116,496.24	-1621	( 2,239,470.04)	15870	12,232,992.48
42	9999	7148	7,687,892.50	8639	9,179,029.00	1491	1,491,136.50	17278	18,358,058.00
43	9000	18775	129,516,442.96	36132	173,285,057.74	17357	43,768,614.78	72264	346,570,115.48
44	9900	0		60633	131,437,288.77	60633	131,437,288.77	121266	262,874,577.54
45	Grand Total	38083	151,317,388.02	113339	320,017,871.75	75256	168,700,483.73	226678	640,035,743.50
Sheet1