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.

 

Comments (17)

  1. Anonymous says:

    it would be great if you could tell us where to find this ‘sort by column’ button so after reading this, one doesn’t have to spend however long looking for it.

  2. Anonymous says:

    That was amazing. I wasted a week trying to find a workaround for the sorting problem.
    By the way, the data type has to be text (not Date), or PowerView will place the columns in a freaky continuum of dates.

  3. stan says:

    It would be very helpful to mention what version of PowerPivot was used and how to get to the button.

  4. Seetharamu Shroff says:

    Thanks for the tip and this is works like a wonder.

    By Default this Sorts in Ascending order for example the Months 1 to 12, I have a situation where I need to Sort by Column in Descending Order and I have a hard time getting this done. what is the way out?

  5. JC says:

    I don't think was mentioned, but if you have a month number column in your PowerPivot data table, make sure to check the "Data Type" setting of the month number column.  Mine always comes in as "text" and thus wont sort correctly.  I changed the Data Type to "Whole Number" and voila, the months in my pivot table sorted correctly.  I need to check my cube and make sure the field is set correctly there now too.

  6. Brandon says:

    Hi,

    Thank you so much for this tip. It’s solved my problem completely!

  7. George says:

    Stan asks that you mention the version of PowerPivot version used and how to get to that version. Stan, I’m running Excel 2010. I’m not certain which version of PowerPivot I downloaded, but it was recent (after Jan. 2014). On this version you can find the Sort By Column button by going into the PowerPivot window, select the “Home” tab, and look for the Sort And Filter section of this ribbon.

    Does anyone know how to find the version of PowerPivot that has been added to Excel?

  8. bsdbvae says:

    La lettre envoyée au CIO doit les signatures de Aubut et maire de Toronto John Tory afin que la ville d’être un candidat officiel pour 2024. Une tentative réussie aura aussi besoin du soutien des trois ordres de gouvernement et des commanditaires.
    http://jeuxhack.net/clash-of-clans-hack/

  9. itgwrgew says:

    The activation code in clash of Kings is a practical means to immediately reach large amounts of gold and resources.
    http://newclashofclanshack.com/

  10. FAscae says:

    http://gpsphone-tracker.com/
    Of course we know that its marketing was initially uses only, which on the one hand gave me nerves, definitely allowed me to read and watch the first review of the product.

  11. caxce12 says:

    Per i non informati, l’aggiornamento di settembre offerto modifiche come più forte Lightning incantesimi ai livelli 5 e 6, nonché Municipio 8 a 9 allo stesso livello. Inoltre, Municipio 10 può ora aggiornare incantesimi fulmine al livello 7 e aggiungere
    anche il veleno, terremoto e congelare gli incantesimi.
    http://giochitrucchi.com/trucchi-clash-of-clans-clash-of-clans-mod-gemme-illimitati/

  12. Semy says:

    Of course we know that its marketing was initially uses only, Helpful thanks
    http://www.freecdkeys.org/clash-of-clans-hack-free-gems-elixir-gold-android-ios/

  13. JFGHSR says:

    As the doctor Trevino Mora explained it, in a first phase the mathematical model adapted it and semejó with serum conditions of value variables; in a second phase approaching biological models that regulate and approach with the human model; and in a third
    stage is expected to be assessed directly to the human model with diabetic patients controlled or uncontrolled completely and thus have an equation that can move as much as possible to the real value.
    http://gps-phonetracker.net/gps-phone-tracker-mobile-tracker/

  14. badvae says:

    E infatti, questo non era l’unico esempio di superarma lottando per adattarsi alla sua nuova dimensione. Con solo sette lavoro presso lo studio – "due ingegneri, tre artisti, un game designer e me," Ajami confims – conoscendo i suoi limiti è diventato
    la più grande lotta.
    http://giochiditrucchi.net/trucchi-clash-of-clans-clash-of-clans-hack/

  15. csacasc says:

    With Android Wear smart watches are more compatible with the Android operating system quality. Now has come a new generation of watches with this operating system, and therefore those of the previous generation are watches more affordable, yet almost having
    the same functions. Here are 3 clocks with Android Wear that you can buy for about 150 euros.

    http://phonetrackerfree.net/phone-tracker/

  16. JB says:

    Excellent. Just what I needed.