Sorting PivotTable Data with Multiple Fields in the Values Area

Justin

Justin

New member
Joined
Mar 22, 2011
Messages
13
Reaction score
0
Points
0
Hi all,

I'm working with a PivotTable and having trouble sorting -- I have an idea why it's not working but thought I would check.

Here is a replica of the sheet I'm working with (the data and fields are all fictitious but the structure is the same):

View attachment Pivot_Table_Sorting.xlsx

In rows we have sales reps with a forecast and actual revenue for each. The revenue field appears in the values area three times, calculated in different ways.

The first is a straight-up sum of all revenue for the year, so it appears for both forecast and actual.

The second two fields show the difference from forecast to actual for each sales rep -- so of course these columns only have values for "actual" since the forecast has zero variance from itself.

My challenge comes when I try to sort by either the % Diff. from Forecast or $ Diff. from Forecast fields. The PivotTable just won't let me do it and sorts instead by the Yearly Revenue field.

This happens both when the table is set up with its current structure as uploaded or when I put the Forecast/Actual field above the sales rep field. I've tried all the different ways of accessing the sort menu, but when I select in custom sort to sort sales reps according to % Diff. from Forecast descending, it just defaults to Yearly Revenue again.

My hunch is it won't let me do it because not every row has a value for that column and so it doesn't know how to sort it.

But it's quite inconvenient I think, as it doesn't allow you to easily see which reps have the biggest variance.

Am I missing something?

Thanks for any insight.
 
Hi Justin
Sorry to be a while coming to this. Ken gave me a "nudge" to say that this question needed answering.

It can be done, as you will see in the attached file, but the method is far from intuitive!!!

I haven't bothered with your formatting, as you can deal with that for yourself.

I dragged Sales Rep to Row area
Forecast to Column Area
Revenue to Values
Revenue to Values as Difference from
Revenue to Values as 5 Difference from
I then hid columns E and G As they are the blank columns with zero difference from Forecast.

Then right click on any Sales Rep>Sort>More Sort Options
Select Descending>from the dropdown select Diff %
Click More options and select
Sort by>Values in selected column>$F$6 (the first %Diff value)>OK>OK

For some reason, it always defaults to $B$6, but if you change to $F$6 (or whatever column you want to sort by, then it works)

I would think that this is a bug and I will report it as such.
 

Attachments

  • Pivot_Table_Sorting.xlsx
    25.7 KB · Views: 2,877
Thanks for picking this up, Roger.

I stopped when I got to hiding the blank columns thinking... Really? :)

Always nice to get a second opinion to know I might actually not be missing something!
 
Hey Roger,

Thanks a lot for the help.

I tried to replicate your steps exactly, but I still can't get it to work for some reason.

You'll see in the screenshot below, when I go:

Click More options and select
Sort by>Values in selected column>$F$6 (the first %Diff value)>OK>OK

It says, "Sort Sales_Rep by % Diff in descending order using values this column: Actual" just beneath it.

But then when I press OK>OK it just seems to ignore it and still sort by the Revenue: Actual column ($B$6) instead. I tried this 3 or 4 times and no luck.

When I go to your pivot table that you added to the workbook and check out the sorting options to compare, it seems to indicate it has been sorted manually. Super weird...

Please let me know if you can see anything I'm doing wrong.

sorting_problem1 copy.jpg

Many thanks.
 
Last edited:
Hi Justin

Yep, it does say sorted Actual in the dialogue but ignore that - as I said there is a bug.
The only difference I can see in your screenshot, compared with mine (and I'm sorry I hadn't mentioned it), is I have un-checked the "Sort automatically every time report is updated" box.

Each time you do it, you have to go through the full procedure of first selecting Ascending or Descending, by the relevant field, as it will always default back to Manual.

It definitely does work, and I can happily switch back and forth between sorting by name, sorting Ascending by Diff%, sorting Descending by Diff $.

I did my original file with XL2010, but I have just confirmed that the outcome is identical under XL2007.
 

Attachments

  • Capture.jpg
    Capture.jpg
    13.6 KB · Views: 1,495
  • Capture2.jpg
    Capture2.jpg
    12.1 KB · Views: 1,484
Last edited:
It worked!

That checkbox for "sort automatically" was the clincher. How satisfying to see it sort properly now :)

Just wondering, is it a good best practice to usually leave that box unchecked, or is it just in rare situations where it's necessary?

Thanks again.

Justin
 
Hi Justin
In normal circumstances I wouldn't go anywhere near the More Options - so just leave the box to it's own devices.

As I said earlier, I believe this to be a bug in XL2007 and XL2010 and I will report it back to the Excel team.
 
I am trying to do much the same thing, only finding no success with the steps mentioned in this thread (and in fact, that's what I tried first off).

I have a file with a field (dollars) for multiple dates. I filtered to select two dates and added a second copy of the dollars field only it is a "difference from" field based on the first date. Sorting on the difference produces random results. Here's an image:

still broken.jpg

Any ideas would be appreciated.
 
Hi Randy

From your screenshot, I see you still have the Sort automatically box checked.
If you read through the thread, I said that I found that this needed to be unchecked.
Give that a try, and if it doesn't work, then upload a copy of your workbook and I'll take a look.
 
Well dang. I thought I had tried it both ways. Thank you very much, that seemed to do the trick.
 
Still doesn't *quite* work across the entire pivot table...

Roger Govier said:
Hi Randy
From your screenshot, I see you still have the Sort automatically box checked.
If you read through the thread, I said that I found that this needed to be unchecked.
Give that a try, and if it doesn't work, then upload a copy of your workbook and I'll take a look.
Click to expand...

A new bump in the road. That failure to click the check box did cause my old problem, but didn't complete the desired task.

I was sorting on the second of two rows in the pivot table, because within each top-level row, I wanted the subordinate rows to be sorted by the Difference.

And that works (see left two thirds of image below) until I hit a row break. Then the second-level rows are not sorted. And if I select one of the items within that grouping and do the same sort, the sort for the top section (the part that's highlighted in green) goes haywire.

It appears that the solution only applies to a single group within one level. Is there a way to get this sort to work globally?

Sorting on Difference row break.jpg
 
Here is a version of the file that will only sort the first grouping

I just cut and cut the file down to fit the size limitation, so it's not quite the same, but I think I got it attached. You should see that the "pattern" field has been sorted by "Diff", descending, based on column E, as noted in your instructions. (Of course, when you go into it, the dialogue box will have reverted to the default as it always does, but you should see that the patterns are sorted on column E). But only up to a point. At lines 61 and 65 and so on, the pattern sort reverts to garbage. In other words, only the first grouping of patterns sorted based on the "Diff" column (which is the difference of a field based on a particular date).

Have you seen a workaround that will sort the entire field (in this case Pattern), under each header field (in this case Business Unit)?
 

Attachments

  • Workbook for Excelguru.xlsx
    391.3 KB · Views: 17
Hi Randy

Apologies for the delay in response but I have been away from my desk for a few days.

It would appear that the problem is that Excel gets confused when there is a change in Business unit.
I tried taking BU to the Filter area of the PT, performing the sort and it appears to get everythin in the correct order, than I took BU back to the first item in the Row area.
As far as I can see this works OK.

To save the hassle of doing this each time, I have written some code (see below) which is within the attached workbook where I added it to a button on the Pivot sheet.

I hope that this helps

Code: 
Sub SortbyPattern()Application.ScreenUpdating = False
    With Sheets("Pivot")
        With .PivotTables("PivotTable1").PivotFields("Business Unit")
            .Orientation = xlPageField
            .Position = 1
        End With


        .Range("$A$8").Sort Key1:="R8C5", Order1:=xlDescending, Type:= _
                            xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom


        With .PivotTables("PivotTable1").PivotFields("Business Unit")
            .Orientation = xlRowField
            .Position = 1
        End With
    End With
Application.ScreenUpdating = True
End Sub
 

Attachments

  • Workbook for Excelguru#2.xlsm
    378.1 KB · Views: 37
Thanks for the VB code. I'll have to decipher it and see if it can be added to the automated build of this file, but it looks a bit more promising, as long as users don't drag filters down into the rows (which they sometimes do).

I wonder why basic Excel doesn't handle this fairly simple condition without resorting to coding?
 
You must log in or register to reply here.
Back
Top