Log In
Register
Home
April 25, 2017
Offline
OK, I had this issue when creating a standard pivot table from Power Query, but since I was able to recreate this issue from a standard table (directly into a standard pivot table), I thought I'd post this question here.
The issue is, as stated in the title, that I can't seem to sort the pivot table into the sort order that I want to (from larges to smallest). I know the mechanics of how to do it, but for this example, it doesn't work as expected. The source data is in the Data tab and I produced the pivot table from it (in the Pivot tab). The cells in yellow show the rows that are not sorting as expected (they should be at the bottom of the table).
What I'm essentially showing in the table is some raw department costs, like HR, and the walkup of their cost assignment to other departments. For example, since HR is not a profit center, I'm assigning a percentage of it to other departments (whose names I've omitted to keep the pivot table as simple as possible). I've seen others on different websites struggle with being able to sort by a grand total, but the suggestions that I've tried don't seem to work here.
So, I'm wondering if this can be done at all within the pivot table realm?
Thanks,
Cory
April 25, 2017
Offline
In thinking about this further, and doing more searching, I found that if one adds a hidden helper column, which is the concatenation of all the rows fields (so to create a unique key in a way), and then sorting on that key, one can indeed sort by the Grand Total. I believe the issue arises when there is more than one row field. Although this makes sense, I found the solution elsewhere; I didn't come up with this.
The question is whether there are any other easier solutions than this albeit easy workaround?
VIP
Trusted Members
June 25, 2016
Offline
Hi Cory
My suggestion is to create a helper column in your data to make the Orig Department (the source of the problem) unique.
If you don't like the numbers behind the Helper column, you can add spaces behind it instead with
=A2&IF(COUNTIFS($A$2:A2,A2)=1,"",REPT(" ",COUNTIFS($A$2:A2,A2)-1))
This will make the Helper column "looks like" the Orig Department.
Hope this helps.
Sunny
1 Guest(s)
