
Power Query
Power Pivot

July 11, 2016

Hi,
I have a pivot table that I want to apply a manual sort to. I have done this but the issue is that every time the table is updated then the manual sort is lost. I have tried clicking/unclicking all options I know of that relate to the sort, pivot table options - use custom lists when sorting, More sort options - Sort automatically every time the report is updated, but they have no effect.
It seems that the issue relates to the fact that the pivot table is using the data model to get the data. I have attached a file that shows examples of a pivot using the data model and one that doesn't. In the file I have manually sorted both pivots on the Product Class column in the order D,B,A,C. If you then filter the pivots either using the column filter or the slicers then remove the filters you will see that the one using the data model re-sorts as A,B,C,D whereas the other one retains the manual sort.
Does anyone know how to resolve this?
Thanks
Bax

VIP

Trusted Members

December 7, 2016

Hello,
There are good support articles at Microsoft, for example this one about sorting data in PivotTables or -Charts.
At the bottom of the page you will find following info:
Note: A custom list sort order is not retained when you update (refresh) data in your PivotTable.
You can always add an index column to your data table where you set values which can be used to sort by in the PivotTable.
Br,
Anders

VIP

Trusted Members

December 7, 2016

1 Guest(s)
