none
PowerPivot for Excel 2010: Problem sorting the grand total row

    General discussion

  • I am working in PowerPivot 2010 and am having difficulty with sorting the grand total row.

    Very often I create tables that count cases, and using the same field, calculate the % breakout.  What I have found is that when I sort the grand total row by descending order (rather than the default alphabetical label) the sections sort differently than expected.  I am using a simple example below to show what I am finding.

    The default sort is alphabetical by field label - Adult, Child, Retired (for both Count and Percent sections).  If I sort by descending on grand total row in the Count section the results are Adult, Retired, Child.  The count section sorts as expected, but the percent section appears to sort randomly (I would expect the sort to be the same as the count section).  To try to correct, I sort descending on the grand total in the Percent section, which results in Adult, Retired, Child as expected, but now the Count section appears to reorder randomly.

    Sample data:

                             Count                                                         Percent

    ServArea           Adult            Child         Retired                Adult               Child               Retired

    Area OSA            516,338       56,489      424,019                51.80%          5.67%              42.54%

    Area PSA              12,028            415          8,398                57.71%           1.99%              40.30%

    Area SSA              15,892            953        19,448                43.79%           2.63%              53.59%

    Grand Total        544,258       57,857      451,865                51.64%           5.49%              42.87%

    This functionality is different than in normal Excel pivot tables, where both sections would have the same sort whether it be alphabetical by field or by grand total row.   The unexpected sorting behavior above is also found in 2013 PowerPivot and so upgrading to 2013 would not fix this problem. Any help understand why the sort works this way, and more specifically how to get PowerPivot to sort both sections the same would  be much appreciated.

    Tuesday, August 27, 2013 7:16 PM

All replies

  • Hi,

    I am not sure that I understand the problem, are you saying that the values are sorted differently for Count and Percent? The table you show looks ok to me..

    Thanks,

    Kasper de Jonge

    Thursday, August 29, 2013 6:01 PM
  • Kasper:

    If I sort descending on grand total row in the count section, below is what results: Adult, Retired, Child.  You can see the count section sorts as expected, but the percent section appears to sort randomly (I would expect the sort to be the same as the Count section)

                           Count                                                         Percent

    ServArea        Adult            Retired              Child            Child              Adult              Retired

    Area OSA       516,338         424,019           56,489                5.67%           51.80%          42.54%

    Area PSA         12,028             8,398                 415               1.99%            57.71%         40.30%

    Area SSA         15,892           19,448                 953                2.63%           43.79%         53.59%

    Grand Total   544,258         451,865            57,857               5.49%            51.64%          42.87% 

    As you can see it sorts the Count correctly, but the Percent section is in a random order.  If I try to sort the grand total by Percent it shows the Percent section in the correct order, but randomly orders the Count section.

    ChrissyB

    Friday, September 20, 2013 5:57 PM