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.
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.
- Changed type Ed Price - MSFTMicrosoft employee, Owner Friday, September 13, 2013 6:37 PM We need the OP to answer questions in order to give an answer
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.