Repairing Month Sorting Order in Power Pivot – Part 2

Here is the Power Pivot way of customizing sort order for any column. We will continue with the month sorting order example.

Read the previous article first.

Estimated reading time 8 min

Problem Recap : Month sorting order

Power Pivot pivot table shows months in alphabetical order. In the previous article, we saw how to solve this problem using Custom List based sort.

In this article we will see a little more complex but more flexible method.

Download and Follow Along

This file has only one sheet with both raw data tables. These tables have already been added to data model.

Solution 2 : Month sorting order

The steps are: Create a relationship between both tables using the common month column. Use the MonthKey column in the final Pivot Table. But before doing that, set the column ordering based upon the MonthNumber column.

Open Power Pivot Data Model.

The Power Pivot window shows two base tables Data and Months.

Create relationship between Data and Months tables.

Right click in the Month column header in Data table and choose Create Relationship.

Choose the Months table and MonthKey column in the dialog to create a relationship.

Set sort order in the Months table

Click the Months table tab. In the ribbon, choose Sort By Column from the Home tab.

Now inform Power Pivot that whenever the MonthKey field is used in pivot table, the sorting should be as per the MonthNumber field. This is a very useful feature.

Remember that any field within a table can be used as the sort order for any other field.

Cross-referencing is not allowed. For example, in this case I cannot create another Sort By Column  from MonthNumber to MonthKey because they are already involved in sorting each other.

Create Power Pivot Table

From the Home tab of Power Pivot window choose Pivot Table. Create it in a New Worksheet.

Now drag Amount to data area and Monthkey into the row area. Things work perfectly as expected. This happened due to the Sort By Column option.

Other uses of this method

We can use it for custom sorting any master files. Often we want locations in a custom order – where the head office location comes first and then other locations appear in descending order of importance (or revenue or headcount or whatever).

Similarly, when viewing products or Cities or Expenses or Training Topics, we often want a custom order which is neither ascending or descending. This is when you add a column like MonthNumber which contains the desired order of sorting and map it to the Descriptive field.

Here is a sample table for sorting products in the desired order.

Is there an even better way?

What I just showed is a very simple example to explain the concept. However real life data is much more complex. Typically the transactional data will not have a MONTH column. It will be derived from a Date column which contains a full date (and sometimes time portion as well).

Date related calculation can be really complex. Besides we want to group them by many different periods like days, weeks, months, quarters, years, fiscal years and so on.

In this case, adding a calculated column for all of them is not a good idea.
Why? Because if the data is large, that calculation for every row is an overhead.

So what is the right way? We must use a TIME table.

We will discuss the concept of Time Table and how to use it in the next article.

***