Pivot table won't sort how I want when more than one field appears on the 'Row'.

[Rhino_Dance]

'Exhibit 1' below is taken from a pivot table. I have two fields in the 'Row' section of the pivot table which are 'Region' and 'Sales_Rep'. My value is 'Revenue' and it appears in the column marked 'Total'. (The numbers on the left are row numbers which can be ignored.) I want to sort the pivot table by descending Revenue. However, when I do this sort, I get a separate sort for each region. I don't want this. I simply want all the rows in the pivot table to appear in descending order by revenue irregardless of Region. What I want is shown in Exhibit 2 below.

After many attempts I have concluded that there is no way to do a simple sort like this when there is more than one field on the 'Row'. Is this true? If so, what are my options in generating the sort that I want while still maintaining my report as a pivot table? Thanks.

Exhibit 1: THIS IS WHAT I DON'T WANT. Sorting within each Region.

	B	C	D
12	Region	Sales_Rep	Total
13	A	Sara	$169,856.00
14		Rachel	$167,989.00
15		Chris	$161,945.00
16		Will	$156,770.00
17		Jessica	$154,323.00
18		Rick	$152,894.00
19		Kevin	$152,387.00
20	B	Kevin	$175,686.00
21		Jessica	$164,684.00
22		Will	$159,550.00
23		Rachel	$157,616.00
24		Chris	$153,524.00
25		Sara	$149,359.00
26		Rick	$144,315.00
27	C	Chris	$191,361.00
28		Rachel	$188,697.00
29		Jessica	$179,164.00
30		Kevin	$169,003.00
31		Rick	$167,765.00
32		Sara	$160,515.00
33		Will	$141,774.00

<tbody>
</tbody>

Pivot2 (2)​

Exhibit 2: THIS IS WHAT I WANT. All records sorted by Revenue, irregardless of region:

Excel 2010

	I	J	K
12	Region	Sales_Rep	Total
13	C	Chris	$191,361.00
14	C	Rachel	$188,697.00
15	C	Jessica	$179,164.00
16	B	Kevin	$175,686.00
17	A	Sara	$169,856.00
18	C	Kevin	$169,003.00
19	A	Rachel	$167,989.00
20	C	Rick	$167,765.00
21	B	Jessica	$164,684.00
22	A	Chris	$161,945.00
23	C	Sara	$160,515.00
24	B	Will	$159,550.00
25	B	Rachel	$157,616.00
26	A	Will	$156,770.00
27	A	Jessica	$154,323.00
28	B	Chris	$153,524.00
29	A	Rick	$152,894.00
30	A	Kevin	$152,387.00
31	B	Sara	$149,359.00
32	B	Rick	$144,315.00
33	C	Will	$141,774.00

<tbody>
</tbody>

Pivot2 (2)
​

 

[Jerry Sullivan]

I don't think one can do exactly what you describe with a PivotTable.

A barrier is that the PivotItems in the top level of the Row Labels (in your example, the top level is "Region") need to remain continguous. Their order can be rearanged, but all the "A's" have to stay together along with the "B's" and "C's".

One workaround would be to create a top level above region that had only one row per PivotItem. An easy way to do that would be to add a PivotField to your SourceData that has the Region and Names concatenated with a unique delimeter.

Excel Workbook
	A	B	C	D
1	Top Level	Region	Sales_Rep	Total
2	A|Sara	A	Sara	$169,856.00
3	A|Rachel	A	Rachel	$167,989.00
4	A|Chris	A	Chris	$161,945.00
5	A|Will	A	Will	$156,770.00
6	A|Jessica	A	Jessica	$154,323.00
7	A|Rick	A	Rick	$152,894.00
8	A|Kevin	A	Kevin	$152,387.00
9	B|Kevin	B	Kevin	$175,686.00
10	B|Jessica	B	Jessica	$164,684.00
11	B|Will	B	Will	$159,550.00
Sheet

By making the "Top Level" field the first field in the Row Labels Area, it will allow you to sort by the Total field.

The extra field can be hidden. Not an ideal solution, but I'm not aware of a better way to sort a Pivot Table as you describe.

Another alternative would be to copy-paste parts of the PivotTable and sort as a standard worksheet range..which has a different downside.

 

[Rhino_Dance]

Jerry,
Thanks very much for your help. The idea of sorting on a hidden concatenated field is a great workaround which I will use. The one downside is that since the concatenated field is on the top level of the row, it shares the same column as the filters, so the filters will always be hidden with this method as well. I appreciate your assistance. -John

 

[Jerry Sullivan]

John,

The hidden column should align with the cells that display Report Filter's field names, but not the dropdown for the filter.
You can copy the field names to the right of the dropdown, or even use formulas to reference the hidden cells that have the field names.

 

[Rhino_Dance]

Jerry,
Thanks, that's a great idea. I might also build in a button that allows the user (or me) to hide and unhide the columns while doing this. I've also thought of a macro that takes the data from the pivot table, copies it to a new sheet and performs the desired sort. I appreciate your help.-John