Share via

PowerPivot: Sort by column

  • Article

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

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    June 19, 2013
    It would be very helpful to mention what version of PowerPivot was used and how to get to the button.

  • Anonymous
    July 02, 2013
    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?

  • Anonymous
    November 01, 2013
    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.

  • Anonymous
    January 20, 2014
    Sorting in Pivot reports,http://www.kettic.com/winforms_ui/csharp_guide/pivotgrid_sorting.shtml

  • Anonymous
    March 22, 2014
    Hi,

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

  • Anonymous
    March 27, 2014
    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?

  • Anonymous
    August 04, 2015
    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/

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

  • Anonymous
    September 22, 2015
    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.

  • Anonymous
    September 29, 2015
    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/

  • Anonymous
    October 04, 2015
    The comment has been removed

  • Anonymous
    October 18, 2015
    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/

  • Anonymous
    November 04, 2015
    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/

  • Anonymous
    November 11, 2015
    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/

  • Anonymous
    December 16, 2015
    Excellent. Just what I needed.