Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Sorting PivotTable Data with Multiple Fields in the Values Area

  1. #1

    Sorting PivotTable Data with Multiple Fields in the Values Area



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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):

    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.

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    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.

    Click image for larger version. 

Name:	sorting_problem1 copy.jpg 
Views:	1564 
Size:	92.9 KB 
ID:	65

    Many thanks.
    Last edited by Justin; 2011-05-08 at 09:31 AM.

  5. #5
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.jpg 
Views:	1313 
Size:	13.6 KB 
ID:	66   Click image for larger version. 

Name:	Capture2.jpg 
Views:	1306 
Size:	12.1 KB 
ID:	67  
    Last edited by Roger Govier; 2011-05-08 at 04:45 PM.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  6. #6
    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

  7. #7
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  8. #8
    Thanks Roger!

  9. #9
    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:

    Click image for larger version. 

Name:	still broken.jpg 
Views:	281 
Size:	101.0 KB 
ID:	3133

    Any ideas would be appreciated.

  10. #10
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •