Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

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:

image

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…

   image

2.  In the Sort dialog box select the Manual (you can drag items to rearrange them) option and then click OK

   image

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

   image

4.  Now click on that cell and drag it to the desired spot where you would like it to be displayed

   image

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

   image

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.

   image

There you go.  Just another option that you have for sorting items in your PowerPivot reports.

UPDATE (11/22/2010) – I have another blog posting here in regards to sorting that you might find interesting as well – Sorting PowerPivot Labels Continued
Advertisements

6 Responses to “Sorting PowerPivot Labels Manually”

  1. […] they will now see this: https://denglishbi.wordpress.com/2010/06/23/sorting-powerpivot-labels-manually/ […]

  2. Lynda said

    The manual sort does not work once a pivot table is refreshed with a slicer.

  3. […] Manual Sorting in Excel […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: