CODEWITHSUNDEEP
excelsortingpowerpivot
user3115933
user3115933

Reputation: 4453

Why is my Excel Power Pivot not maintaining the order of the manually re-arranged items in my ROW field?

I am using Excel 2013 and I have a PowerPivot which refreshes the data from an SQL database.

I have designed a pivot table from the data but I am getting really frustrated at one aspect of the design. I have dragged a field named "Room Category" in the ROW field, dragged a specific field in the VALUE field. I have then added another field as a Slicer, which is basically acting like a filter. I have re-arranged the "Room Category" field on my pivot table manually. However, each time I click on a different item in the Slicer and come back to the original item on the slicer, the order of the "Room Category" is set back to the default alphabetical sorting order!!

I have been looking for a solution all over the web but to no avail!

I am adding below an extract of the data that is being used to create the pivot table:

Property    RoomCat    Order
  A2          X          2
  A2          Y          1
  A2          Z          3
  B5          C          1
  B5          D          4
  B5          E          2
  B5          F          3

So when I've created my pivot table, I have Property as a Slicer and I have manually re-arranged the RoomCat to show as follows for Property A2: Y,X and Z. I've done the same for Property B5 to show as follows: C,E,F,D However, when I click on Property B5 on the Slicer and the click back on Property A2 on the slicer, the RoomCat for Property A2 now shows up as: X,Y and Z

Upvotes: 0

Views: 12203

Answers (2)

AntonioG
AntonioG

Reputation: 35

I think I understand the issue (I've had the same problem with Dates as Text). I think if you create a "Named Set" based on the row Items, it will work.

Upvotes: 0

Gordon K
Gordon K

Reputation: 824

The PivotTable will always sort alphabetically unless you give it something else to sort by. If you add a new field to your data which contains the sort order, then you can tell PowerPivot to use that as the sort order.

First Select your Room Category column, click on the Sort By Column button, then select the column that contains your sort order:

enter image description here

And the result:

enter image description here

Upvotes: 1

Related Questions