A PowerPivot Sort By Any Other Name


The past several weeks I have been looking at the new features of PowerPivot 2012.  I also mentioned this past Tuesday, that the new Microsoft Office 2013 version of Excel includes both PowerPivot and Power View as part of the install.  They just need to turned on.  So this week I’m continuing my Contoso example with Excel 2013 and PowerPivot running on Windows 8.  Therefore, you may notice some differences in the look of the Excel spreadsheet.  Don’t fear.  The techniques  I discuss here work equally well if you are using Vista with Excel 2010 and PowerPivot 2012.

Today I am going to look at a problem that we solved a different way several months ago.  That problem is how to sort month labels chronologically rather than alphabetically.  Back on November 6, 2011 (Wow, was it really that long ago?) I described a way to create a custom sort order for months.  While that method still works in PowerPivot 2012, there is a new way to sort a column by using another column.  Let’s see how that works.

I begin by opening my Contoso 2012 sample data and create a simple pivot table that shows total sales by month.  As shown in the figure below, when I drag the calendar month label field into the rows, the rows appear by default sorted alphabetically.  Unfortunately, that is not a good sort order for months.

To fix this sort order, return to the PowerPivot window and open the tab (table) containing the dates.  In Contoso, this is the dimDate table.  In the Home ribbon find the Sort and Filter option group.  A new button in this group is labeled Sort by Column.    You could immediately click on this button to open the sort dialog.  However, I recommend first clicking on the column for which you want to define the new column sort, then click the Sort by Column button.

This action opens the following dialog and pre-populates the Sort column with the column that you selected.  This saves you a step.  However, if you forget, you can always use the dropdown arrow to display all fields in the current table and select the column from the list.

Next select the column you want to sort by and click the OK button.  You may be tempted to sort by the existing column CalendarMonth.  However, this will generate an error because there are multiple values for CalendarMonth for each value of CalendarMonthLabel because CalendarMonth has the format YYYYMM.  In other words, July would match not just to 07 or 7, but rather to 200507, 200607, 200707, and 200807.  You cannot match the sort column to multiple values in the sort by column.

Therefore, I created a simple calculated column named Month which only consists of the month number as shown in the next figure.

Now I can return to the Sort By Column dialog and create a sort relationship between CalendarMonthLabel and Month as shown in the following dialog.

When I click OK and return to my pivot table in the Excel spreadsheet, I will see that the table automatically refreshes and now displays the month labels in the expected chronological order that makes sense for most users.

That’s it for this week.  A short, simple, but very useful way to sort any column by another column in PowerPivot 2012 or Excel 2013.

C’ya next time.

Advertisements

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