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?
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!