fbpx
Site logo white shadow

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.

repairing month sorting order in Power Pivot

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.

SNAGHTML1828bae4

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.

SNAGHTML182ab194

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

image

Set sort order in the Months table

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

SNAGHTML182fa40f

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.

image

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.

image

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.

image

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.

***

One Response

Queries | Comments | Suggestions | Wish list

Subscribe to Blog

Join 1,768 other subscribers

Popular articles

Pivot Table Pro Course

Yes. You use Pivot Tables everyday. Now it is time to find out the real power and nuances. 5.5 hours video, exercises, samples, Q&A.

Excel to Power BI Course

Learn Power BI using the concepts you already know in Excel. Fast transition, in-depth coverage and immediately usable.