<

Sorting months chronologically and not alphabetically in a Pivot Table report based on Power Pivot data

Published on
15,580 Points
5,880 Views
2 Endorsements
Last Modified:
Awarded
Here's our problem. When you create a Pivot Table in Excel, you can Group that field by month and the sort will be logical (January, February, …). But when you create a Pivot Table based on Power Pivot, the grouping does not work! So you have to get to the month names by a different road. We do this by a Format function in PowerPivot, but the problem is that when you put this field in a Pivot Table, it gets sorted alphabetically. This is logical since the values are text and have nothing to do with dates as far as that Pivot Table is concerned, but this is a problem since the months are not sorted chronologically. This article will tell you how to achieve that.
Let’s start with a simple table in Excel that has only two columns. One has Date values and the other has a number of visitors on that date. Now we would like to create a Pivot Table report to see how the number of visitors is spread through the months.

Case 1: Pivot Table report based on an Excel Table

First we create a Pivot Table based on an Excel Table
Image 1The Pivot Table will show the number of visitors by months. But to do this, since we only have Dates, we have to do Grouping by months on the Dates
Image 2And right away we get the desired result.
 Image 3

Case 2: Pivot Table report based on Power Pivot data

First we add our Table data to Power Pivot the easiest way – by using the Add to Data Model command on the PowerPivot tab.
Image 4Now that we have the data in the Power Pivot we can create a Pivot Table report from Power Pivot window. But when we create a Pivot Table and want to see the analysis by months we see we just can’t select the Group command. It is grayed out…
Image 5So to get to months we use a different trick, we go back to the Power Pivot window and create a calculated column using a Format function. This is a PowerPivot rendition of the Text function from Excel. The syntax is the same, just the names differ.
Image 6Excel Version
 Image 7Power Pivot version.
Using the Format function we now get the month names and a new field to create a Pivot Report by. But when we create it, it looks quite disappointing.
 Image 8So the numbers are OK, but the sorting is alphabetical and not the kind we want. To get the sorting right, we have to go back to the PowerPivot window and create a new calculated column using the Month function. This way we now get a month number along each date and month name.
Image 9Now just adding that to the Pivot Table report would get rid of our problem, but let’s not forget that we want the month names as they were, only the sorting is wrong. But now we have all we need.
 Image 10In the Power Pivot window, we select a value in the month name column and then select a Sort by Column command on the home tab and hey, look at that. You can now say that the Month name column will be sorted by Month No. column.
 Image 11Doing that has changed our Pivot Report instantly
 Image 12And we are one step closer to eternal happiness.
2
Comment
1 Comment

Expert Comment

by:Mehawitchi
Hi GasperK - Great stuff here for the PowerPivot, but on a totally unrelated subject to this posting, how did you manage to take snapshots of screens while actually clicking on a menu item. For example the screen capture of "Sort by Column"
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Join & Write a Comment

This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month