Information about Microsoft SharePoint Server 2010, SQL Server 2012, Business Intelligence and Office 2010.
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.
It would be very helpful to mention what version of PowerPivot was used and how to get to the button.
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?
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.
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.
Sorting in Pivot reports,
Hi,Thank you so much for this tip. It's solved my problem completely!
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?
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.