Sorting A Column Of PowerPivot Data By Another Column

One of the things that used to drive me crazy about working with PivotTables in PowerPivot’s initial (2008) release was summarizing dates by month. With a standard PivotTable, we can use the built in Group functionality to group dates by Years, Quarters and Months. But in PowerPivot, that functionality wasn't implemented. To deal with this, we have to provide our own date table, but the months never really sorted well, and we had to resort to tricks to coerce them into the right order.

In short, I was never happy with my PivotTables when they ended up looking like this:
Click image for larger version. 

Name:	252-1.png 
Views:	15214 
Size:	68.2 KB 
ID:	538

Slicers had similar issues as well:
Click image for larger version. 

Name:	ppvt_sort_02.png 
Views:	15013 
Size:	4.4 KB 
ID:	511

Excel 2010 & PowerPivot 2008 Solution:

To counter this, we pretty much had to add a column of data to our tables that converted our months into the mm-mmm format, which mean that they would show up as 01-Jan, 02-Feb, etc… Doing this would put the PivotTable columns and slicer buttons in the correct order.

Excel 2010 & PowerPivot 2012/Excel 2013+ Solution:

Fortunately, PowerPivot 2012 gave us a way to sort our columns so that they show up correctly in our PivotTables and slicers. Here’s how.

Example 1: Sorting Months

In the PivotTable pictured above, the “Month_Short” column is out of order. To fix this, we enter PowerPivot and pull up the table that has our dates:
Click image for larger version. 

Name:	252-3.png 
Views:	15203 
Size:	37.3 KB 
ID:	539

Next we:

  • Click somewhere in the “Month_Short” column
  • On the PowerPivot Home Tab, click “Sort by Column”

Now we need to figure out which column to sort by. In this case, we want to sort by the “Month_Num” column. January will therefore sort as 1, February as 2, etc:

Click image for larger version. 

Name:	252-4.png 
Views:	14942 
Size:	28.1 KB 
ID:	540
At this point, we can click OK, go back to Excel and refresh our PivotTable:
Click image for larger version. 

Name:	252-5.png 
Views:	14919 
Size:	51.6 KB 
ID:	541

Much better! And it works for Slicers too:
Click image for larger version. 

Name:	ppvt_sort_06.png 
Views:	14973 
Size:	4.3 KB 
ID:	515

Example 2: Sorting Weekdays

Now, what if we wanted to add weekdays to the mix? Can we have more than one sort per table? Of course!
We’d head back in to PowerPivot, find the “Day_Short” column and choose to “Sort by Column” again:
Click image for larger version. 

Name:	252-7.png 
Views:	14932 
Size:	43.2 KB 
ID:	542

Now, the question is, which column do we sort our Day_Short column by? Logically, you’d think it would be Day_Num, as that has the two digit date you’re trying to sort, right?

If you try that, however, Excel will throw an error for you:
Click image for larger version. 

Name:	252-8.png 
Views:	14895 
Size:	21.3 KB 
ID:	543

So what does that mean?

Basically, what it means is that the relationship between the “Day_Short” and whatever it is being sorted by must be a one-to-one relationship. In the case of Day_Num, Excel is complaining because Sunday could be 28 (Jan), 3 (Feb), 10 (Feb), 17 (Feb), etc… Monday could be 29 (Jan), 4 (Feb), etc… In short, Excel has too many options to sort, so it can’t figure it out.

What Excel is looking for is something very clear and consistent to work with. So how do we make it sort as follows?

  • 1 Sun
  • 2 Mon
  • 3 Tue
  • 4 Wed
  • 5 Thu
  • 6 Fri
  • 7 Sun

Well… conveniently, these just happen to be the numbers should in the Weekday_Num column! Every Sunday has a corresponding value of 1, Monday = 2, etc… So let’s sort by that one.

At this point we can return to Excel, Refresh the model, and drop the “Day_Short” onto the PivotTable. Notice that it also sorts correctly:
Click image for larger version. 

Name:	252-9.png 
Views:	14902 
Size:	32.1 KB 
ID:	544

Try this yourself:

If you’d like to replicate the above:

  • You must be running Excel 2010 and PowerPivot 2012
  • Download the sample database
  • Create a new Excel file and open PowerPivot
  • Link to the following tables in the Access database:
    • tblDates
    • qryCOA
    • qryTransactions

  • Create the following relationships:
    • qryTransactions [TranDate] --> tblDates [Key_date]
    • qryTransactions [Link_AccDept] --> qryCOA [Link_AccDept]

  • Create a new PivotTable as follows:

Click image for larger version. 

Name:	252-10.png 
Views:	14897 
Size:	9.1 KB 
ID:	545

  • Using the slicer, restrict your data to Dept 150 for 2009

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts