Hi Mynda,
I have a pivot table and chart that is comparing data from 2019 and 2020. I have sorted the data from highest to lowest number at the year 2020 field.
Example:
2019 2020
Mfg 40 25
Pckg 20
Laboratory 15 15
I added a slicer for the user to be able to filter their area. I came up with the following issue: if the area has data from both years, after de-selecting the filter the data goes back to sorting the 2020 year as originally stated. However, if one of the year has no data, the pivot table sorts out the data in alphabetic order. It's not going back to sorting the data as originally stated (year 20200). Is there a way to ensure that the data is kept sorted at the year I specified?
Hi Victor,
Try setting the field to 'show items with no values' (in the field settings), so that even if there is a year with no data, it is still present in the PivotTable and hopefully it will retain the sort settings.
Mynda
Thanks Mynda,
Unfortunately it did not work. An additional detail, I have items which have data for 2019 but not for 2020 and vice-versa. When I filter one of those is when I have the issue of the table not being able to return to its original sorting.
By the way, your videos have been a great deal of help. I have had learned a lot and have had allowed me to develop nice dashboards. Thanks a Lot!
Hi Victor,
Can you share the file, or at least a sample file?
Mynda
See the file attached.
Graphs in the dashboard sheet that are showing the data sorted from largest to smallest (starting form the bottom), I want them to stay that way.
Any additional recommendation is very welcome.
Hi Victor,
My original suggestion works: "Try setting the field to 'show items with no values' (in the field settings), so that even if there is a year with no data, it is still present in the PivotTable and hopefully it will retain the sort settings."
You need to apply it to the Year field of the PivotTable: right-click one of the Year headers in the PivotTable > Field Settings > Layout & Print tab > check the box for 'show items with no values'.
This will add two more items to your Year slicer: >9/30/2020 and <1/2/2019. These items have no data, so you can delete them from the legend by selecting them (left click twice slowly to select individual legend items), then press delete.
In any Year Slicers you can set it to 'hide items with no data' so they don't display as an option.
You'll also need to fix the colour formatting in your charts as the additional date items will mess it up.
Mynda
Mynda,
Thank you so much. It works indeed. I was not performing your suggestion correctly.
