CODEWITHSUNDEEP
excelsortingpivot-tableexcel-pivot
Lucas
Lucas

Reputation: 51

Excel not sorting numbers correctly on pivot table

It's as the title says, my excel pivot table is not sorting numbers corretly.

When I create the pivot table from my dataset, the goal is to have the data sorted by year, week number, and the Keys sorted by the PM_Value (decreasing order) as shown here. The "PM_Value" column is a Calculated Field.

Year WeekNum Key Value 1 PM_Value Value 2
2023 1 2022-SFS-R757-L05 VAC1 5.777 833 6.936
2023 1 2022-GS7-R125-L05 4.407 833 5.292
2023 1 2022-GS7-R008-L05 VAC 5.347 833 6.421
2023 1 2022-SFS-R735-L05 VAC1 5.757 830 6.938
2023 1 2022-GS4-R007-L06 13.431 829 16.195
2023 1 2022-SFS-R804-L05 VAC1 5.646 829 6.811
2023 1 2022-GS7-R083-L06 4.983 749 6.656
2023 1 2022-GS7-R107-L06 4.941 749 6.600

The problem is that the PM_Value column has zeroes (or possibly errors shown as zeroes), and these values are not sorting correctly. Currently de sorted pivot table is like de one below.

Year WeekNum Key Value 1 PM_Value Value 2
2023 1 2022-SFS-R757-L05 VAC1 5.777 833 6.936
2023 1 2022-SFS-R735-L05 VAC1 5.757 830 6.938
2023 1 2022-GS7-R038-L06 5.055 749 6.753
2023 1 2022-GS7-R081-L06 5.081 748 6.795
2023 1 .... ... ... ...
2023 1 2022-GS7-PRJ52-L12 4.641 33 139.684
2023 1 2022-GS7-PRJ53-L12 4.190 29 144.750
2023 1 2022-SFS-R715-L06 RGD 0 0 0
2023 1 2022-SFS-R719-L06 RGD 0 0 0
2023 1 2022-SFS-R789-L06 RGD 0 0 0
2023 1 2022-GS4-R054-L05 VAC 14.131 811 17.416
2023 1 2022-GS4-R015-L06 15.052 802 18.776
2023 1 2022-GS4-R031-L05 R2 4.856 789 6.156
2023 1 2022-GS4-R058-L06 12.639 731 17.290

I already tried to deactivate de "custom lists" sorting check box on the pivot table options, changing the order of the sort (i.e. Year/Month/Key, Key/Year/Month, etc) but nothing makes the zeroes stay on the bottom of the sorted column.

Has anyone experienced anything like that and could help me?

Upvotes: -1

Views: 940

Answers (1)

Lucas
Lucas

Reputation: 51

The problem was that the calculation of "PM_Value" is a division, and some of the zeroes were DIV/0 errors showing as zeroes and these errors that were messing with the sort. I used a IFERROR(calc,0) on the Calculated Field and the problem disappeared!

Upvotes: 0

Related Questions