none
Bad Sort Order for Pivot Table RRS feed

  • Question

  • I’m having the following problem, a couple of my pivot table date fields are sorting alphabetically despite my having a proper Sort Order Column in the Power Pivot data model.

    Here is a screenshot showing what I’m experiencing: https://www.screencast.com/t/B60E7Hn3iY

    Labeled Parts of Screenshot

    1. Shows how sorting descending based upon “Month and Year” sorts alphabetically and not according to an Index value like I want.
    2. I’ve tried sorting via this interface
    3. And this interface
    4. I’ve confirmed in the data model that the correct columns are specified in the sort order.
    5. I’ve done sorts in the Power Pivot data model by “Month and Year Index” and “Quarter and Year Index” and those sort as expected, it just seems that the “Sort by Column” association isn’t working.

    Other things I’ve tried…

    1. Removing the “Sort by Column” associations, saving, readding them, and trying to sort again.
    2. Confirming that the “Month and Year Index” and “Quarter and Year Index” columns have a data type of “Whole Number” in the data-model.
    3. Removing the columns in the pivot table and readding them.
    4. A variety of other things that haven’t worked.

    Any ideas?  It seems like this is a bug at this point, but I’m not sure what else to do.  It’s driving me nuts that I can’t get this sort order right.


    • Edited by Walter Pelowski Tuesday, June 18, 2019 9:43 PM formatting was wrong
    Tuesday, June 18, 2019 9:37 PM

Answers

  • So it looks like the pivot table is sorting exactly how you told it to. In item 2 in your screenshot you have overridden the sort order defined in the data model and told the pivot table to sort in descending alphabetical name order. This overrides the setting that you defined in items 4 & 5. If you change the setting in item 2 back to "Data Source Order" then it should respect the order you defined in the model.

    If you want is for your months to sort form most recent to oldest then one trick is to multiply your order column by -1  this has the effect of reversing the order (however this can "break" charts as it will also put your most recent month on the left of the x-axis). Unfortunately the Pivot table UI in Excel does not have a way of telling the data model to do ascending/descending by the sort order column, there is just the option to use the sort order defined in the data model or to override it and sort by name.


    http://darren.gosbell.com - please mark correct answers

    Thursday, July 11, 2019 4:06 AM
    Moderator

All replies

  • Excel 365 with PowerPivot and Power Query (aka Get & Transform)
    Sort PivotTable field by year/month.
    With 5 (five) (V) workarounds.
    http://www.mediafire.com/file/zzbn88j51k58ym3/06_21_19.xlsx/file
    http://www.mediafire.com/file/rg73d3zao3t305n/06_21_19.pdf/file

    Friday, June 21, 2019 5:30 PM
  • Hey thanks for your examples of workarounds, but do you know why I have to do a workaround?  Do you know why the Sort By column isn't working in Power Pivot?  I've looked online for articles for indications that this is a bug but haven't found any.  Do you experience the same thing?  None of your examples used a Sort By column in Power Pivot.

    As for your workarounds...

    1. I appreciate the PQ code to pad the date so it can sort alphabetically but I'd really prefer to show just Month Name - Year if possible.
    2. To do the above your Custom List tables are indeed options if I wasn't sharing this report with others and wasn't using dynamic data coming from another data source.  And since the custom lists don't span machines and since even on my own machine I'd have to regularly modify the options in the Custom List.

    So maybe in the short-term I'll just format the date but I really am interested if this functionality is broken for others and if so, does Microsoft know about it and is there a bug listed somewhere?  I want this Sort By functionality to be fixed at some point because it affects a lot of models I've created.

    Wednesday, July 10, 2019 9:25 PM
  • So it looks like the pivot table is sorting exactly how you told it to. In item 2 in your screenshot you have overridden the sort order defined in the data model and told the pivot table to sort in descending alphabetical name order. This overrides the setting that you defined in items 4 & 5. If you change the setting in item 2 back to "Data Source Order" then it should respect the order you defined in the model.

    If you want is for your months to sort form most recent to oldest then one trick is to multiply your order column by -1  this has the effect of reversing the order (however this can "break" charts as it will also put your most recent month on the left of the x-axis). Unfortunately the Pivot table UI in Excel does not have a way of telling the data model to do ascending/descending by the sort order column, there is just the option to use the sort order defined in the data model or to override it and sort by name.


    http://darren.gosbell.com - please mark correct answers

    Thursday, July 11, 2019 4:06 AM
    Moderator
  • Thank you Darren!  This is hugely helpful!  I was under the false assumption that the dialog in part 2 when choosing "Descending (Z to A) by:" would logically use the "Sort by Column" in part 4 to do the sort.  (I guess that's what happens when I assume!)  I thought I had done this before where part 4 correctly inversely sorted the Sort by Column but maybe I mistook it for the experience in PowerBI.

    I see what you're saying about the charts.  To tackle this I created another column for both the "Month - Year Ascending" and "Month - Year Ascending Index" so I can create an alternate table and sort dates for my visuals in ascending order while I sort data for the tables in descending order with a different table.

    This makes much more sense to me now why it wasn't working.  Thank you!

    Thursday, July 11, 2019 1:13 PM