Subscribe to Blog feed
Sorting PowerPivot Labels Manually
Posted by denglishbi on June 23, 2010
One of the items that you are going to run into is the sorting order of items in PowerPivot. For the most part you might get by with items being in ascending or descending order, but there are going to be times when a more manual approach might be needed. One particular case would be the order to calendar month labels in a report. Initially it would be displayed in alphabetical order like the following:
So how can you get this in the order that you are used to seeing the months displayed? As you can see this is not how you want it and it isn’t like Analysis Services where you can define an additional column to sort the data by. One option would be to have an additional sort order column that you could concatenate with the month label like the posting here by the PowerPivot Team – Sorting month by natural sort order. Another option is to use the manual sorting functionality that is available. How does that work? It isn’t extremely obvious and after a bit of playing around I finally figured it out. Here is how you can do it:
1. Right-click the label and select Sort and then More Sort Options…
2. In the Sort dialog box select the Manual (you can drag items to rearrange them) option and then click OK
3. Now in the PivotTable select an item and place the cursor towards the right-hand side of the cell until you get the arrows displaying in all four directions
4. Now click on that cell and drag it to the desired spot where you would like it to be displayed
5. Then drop the item and now you have not only moved the item with this display, but everywhere it is being displayed in the PivotTable
6. Now simply repeat the process until everything is in the proper order that you want for reporting. Note: this is just for this PivotTable.
There you go. Just another option that you have for sorting items in your PowerPivot reports.
So long Live Spaces, Hello WordPress! « Dan English's BI Blog said
[...] they will now see this: http://denglishbi.wordpress.com/2010/06/23/sorting-powerpivot-labels-manually/ [...]
Lynda said
The manual sort does not work once a pivot table is refreshed with a slicer.
denglishbi said
That is absolutely correct. So instead you can use this technique and that should solve the issue http://denglishbi.wordpress.com/2010/09/14/sorting-powerpivot-labels-continued/ until you are using the new release of PowerPivot with SQL 2012 that provides the ability to sort the column of data based on another column (what’s new in PowerPivot – http://technet.microsoft.com/en-us/library/hh272053(v=sql.110).aspx).
Lynda said
I beieve that will only work when sorting by Day or Month. I have an alpha column that I need to have a custom sort.
denglishbi said
Yes, if you are not working with these types of examples then you will be out of luck until v2 of PowerPivot. You would need to incorporate the sort into a calculated column to reference unfortunately for now if you want this to be included in the PivotTable generated by PowerPivot. Otherwise you could possibly try referencing the PowerPivot PivotTable that is generated in another worksheet and do some good old lookups.