+ Reply to Thread
Results 1 to 9 of 9

Pivot table will not return top 10 (Filter)

  1. #1
    Registered User
    Join Date
    02-01-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    15

    Exclamation Pivot table will not return top 10 (Filter)

    Heya there boys and girls.

    Im hoping someone can help me out, this has me pulling my hair out and no one in my office can help me.

    Say:
    A2:A300 - sales numbers ranging from $1,700,000 to $18,000,000
    B2:B300 - sales managers
    C2:C300 - customers
    D2:D300 - states/prov

    I have a pivot table in that place shows:

    The sales manager, then the states, the customers within the states and the sales figures.
    Simple, and easy enough.

    All this sits within row labels, except sales numbers, they sit in both row labels and values because if it is not, when i ask excel to show me the top10 it produces an error. Within values the sales number are told to display the sum not the count.

    When i click on the sort button of the sales numbers under the row labels, click Value Filters, Click Top 10, tell it to sort top 10 based on SUM and click OK, nothing happens.

    However if i click value filters and select something like less then and say less then 5,000,000 everything over 5,000,000 just dissapears from the list! It makes no sence from what i can see!

    ANY HELP AT ALL! Greatly apprecaited!

    Cheers,
    Dale

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot table will not return top 10 (Filter)

    Dale, I'd suggest posting a sample file and outline expected/desired results (obviously dummy names etc)

    It's not really clear at what level of granularity you wish to apply the Top 10 level.

    If you intend to display the top 10 overall aggregates (combinations of manager, state & customer) you could consider adding a key to your source data that concatenates manager, state & customer into a single string.
    Use this key field as Row label and set Sales as data field, apply the top 10 filter to the Row Label and add a subsequent Desc sort

    edit: uploaded example
    Attached Files Attached Files
    Last edited by DonkeyOte; 02-01-2011 at 05:28 AM.

  3. #3
    Registered User
    Join Date
    02-01-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Pivot table will not return top 10 (Filter)

    DonkeyOte,

    Thanks for your help.

    See the attached file for what im trying to achive. If you notice the filter i have applied to the value cell you can see that the filter has had no effect whatsoever.

    Any ideas?

    Sorry for not attaching the attachment to start with, I didnt know I could.

    Cheers,
    Dale
    Attached Files Attached Files
    Last edited by DonkeyOte; 02-01-2011 at 05:46 AM. Reason: removed unnecessary quote

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot table will not return top 10 (Filter)

    Quote Originally Posted by dalew
    Any ideas?
    Did you have a look at the example I posted ? My hunch is that's along the lines of what you're trying to do.

  5. #5
    Registered User
    Join Date
    02-01-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Pivot table will not return top 10 (Filter)

    Okay, I feel like a royal git. That was overly complex, i was trying to sort at the number rather then the company! Frustrating that i wasted practically one full day working on that! (9.13PM Local time)

    A good one for you will be how do i sort by sales number?

    Cheers!

    And thank you SO SO SO Much!

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot table will not return top 10 (Filter)

    Not entirely sure I follow Dale - in terms of what you're filtering and what you want to sort - could you post a further sample ?

  7. #7
    Registered User
    Join Date
    02-01-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Pivot table will not return top 10 (Filter)

    I dont think i need to sample more i need to explain myself better .

    If you look at the example you provided you can see that they are in descending order how could i change that to be say ascending?

    Cheers

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot table will not return top 10 (Filter)

    Probably worth spending time familiarising yourself with Pivot functionality - Debra Dalgleish's site is an excellent resource - eg Sorting Pivots

  9. #9
    Registered User
    Join Date
    02-01-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Pivot table will not return top 10 (Filter)

    Thanks so much for the help!

    Greatly appreciated!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1