Log In
Register
Home
Power Query
Power Pivot
July 11, 2016
Offline
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
Offline
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
Power Query
Power Pivot
July 11, 2016
Offline
Hi Anders,
Thanks for the reply. Unfortunately I don't control the report that the pivot is in and therefore can't add an index to it. I am more interested in why the sort results work differently for pivots from a data model and those not from the model.
Regards
Bax
VIP
Trusted Members
December 7, 2016
Offline
Hello again,
Well, I can't find any explanation to why it behaves like that.
Seems that a regular PivotTable is the better option if you want to have and retain manual sort order.
Br,
Anders
July 16, 2010
Offline
Hi Bax,
The data model can't access custom lists, therefore you add a sort order to the data model and set the 'sort by' column in there.
Mynda
Power Query
Power Pivot
July 11, 2016
Offline
Hi Mynda,
I have had a look at the options in the data model and I can't see where you would add the sort order. Can you help?
Thanks
Bax
1 Guest(s)
