PowerPivot: Sort by column

Sometimes there are measures which have an intrinsic order to them, such as days of the week or month of the year. However, this order is not always known by the technology being used to represent data by those values. For example, if I was to create a pivot table plotting some value against month of the year, the default behaviour of Excel would be to put those months into alphabetical order. The same is true of PowerPivot.

Under the old version of PowerPivot, the recommendation was to change all these fields to have a number before the month name (i.e. January becomes 1. January, February becomes 2. February, etc.). This works, but it's fiddling and annoying to set up, particularly if you're pulling in data from another source that just uses the month name.

Let me introduce to you: the "sort by column" button.

When you select this button, you get a dialogue allowing you to choose to sort one column by the values in another. In the month example, I would have a second column, let's call it "month num", with the numbers 1 to 12 corresponding to the appropriate month.

This means that when I select to include months in my pivot table or slicers, the months appear in the order determined by the month num column (i.e. in the order 1 to 12) instead of alphabetical.

You can also choose to hide the number column from client tools, so users working with the pivot tables or using this as a data source for Power View see the month field, with the months appearing in the right order, and never need to know that the month num column exists.