+ Reply to Thread
Results 1 to 8 of 8

Pivot table Showing top 10 based on Volume without showing volume in pivot table

  1. #1
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Pivot table Showing top 10 based on Volume without showing volume in pivot table

    Hello All,

    Quote often I am wanting to make a pivot table showing the "top 10" in regards to the count of a particular field (Invoice #, to be specific).
    I then want to show the "Total Profit" for those customers over the last 12 months.
    I am unable to figure out how to show the top 10 customers based on VOLUME (the count of the invoice #), without including it in the pivot table.
    I am only wanting the Total Profit for these customers to be shown both in the pivot table, and the pivot chart.

    Does anyone know how this could be done? creating a column called "order count" and filtering by that will also not work, as I want the top 10 to update based on the filters/slicers that are applied (ex. certain products, etc...)

    I am worried the answer is simply "No", but if someone knows either way, some answers would be appreciated.

    Thanks in advance!
    Please click the * icon below if I have helped.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Pivot table Showing top 10 based on Volume without showing volume in pivot table

    Maybe like the solution in this threat => #5.

    http://www.excelforum.com/excel-char...ng-others.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Pivot table Showing top 10 based on Volume without showing volume in pivot table

    Thanks for you response oeldere.
    I think that is a bit more complicated then what I need (perhaps I am not understanding that post exactly).

    I have attached a workbook showing my request. Hopefully that makes it a bit more clear.

    My goal is to make a pivot table that looks exactly like the RED pivot table, without having to hide columns, and without having the "Count of Transaction #" visible in the chart.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Pivot table Showing top 10 based on Volume without showing volume in pivot table

    I see your problem and I don't have a excel solution for it.

    I made the graph by pasting the pivot table (red) as value to another sheet.

    Then i deleted the column count.

    After that I made the graph.

    Not a nice way, but maybe the only option ?

  5. #5
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Pivot table Showing top 10 based on Volume without showing volume in pivot table

    Thanks oeldere. Copy and pasting values wouldn't work, as I want the user to be able to use slicers and have the graph dynamically change.
    I suppose I could create a MACRO so that someone could quickly do the steps you mentioned, but it is still not ideal.

    I think what I will end up doing is simply using INDEX and MATCH to pull the columns I want and have the graph linked to that. I will run into issues with the "top 10" sometimes displaying 11 entries, or 12, etc...., and would need to use the indirect function in specifying the data source.

    Now that I think about it, I could make a column that is all 1s, and simply filter that field by the top 10 (which i am able to do WITHOUT having it included in the table/graph).
    I will attempt that and will post the results.

  6. #6
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Pivot table Showing top 10 based on Volume without showing volume in pivot table

    I run into the same issues as I did previously.

    I think what I may end up doing is
    - include the transaction volume
    - Set it so it is a "% of Column Total"
    - set columns to 100% overlap
    - Hide the transaction volume column(S)

    It isn't ideal, and if I want to quickly add a column header, I will run into issues, but I can't think of a better way.

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Pivot table Showing top 10 based on Volume without showing volume in pivot table

    @melvinrob

    You can add more values to the top 10.
    e.g. you can show the top 15 or below 20 => that's possible in excel 2007 and further.
    It hasn't to be just 10 values (top 10).

    There is a option like calculated field.
    Mabye it can be done by that option.
    In excel 2007 I don't it possible that way (calculated field).

    In excel 2007 there is a possibility of calculated field => but I don't think it is available for your problem.

    Since you use excel 2010 mayby it's possible in that version.

    I read about those this (so I would not be supprised if it is possible in Excel 2010), but since I have excel 2007 I can't test that for you.
    Last edited by oeldere; 07-22-2013 at 11:23 AM. Reason: in excel 2007 there etc.

  8. #8
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Pivot table Showing top 10 based on Volume without showing volume in pivot table

    Oeldere
    I knew I could make it top 5, or 10, or 20, etc....
    And I would run into the same issues with a calculated field, as you are unable to filter by a calculated field without including the field in the pivot table, which wouldn't get me further ahead than simply putting in the Transaction Count Column.
    I think a Macro is necessary, but I'll just use my other solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Pivot table not showing zero values
    By Redder Lurtz in forum Excel General
    Replies: 3
    Last Post: 09-14-2012, 10:01 AM
  2. Replies: 3
    Last Post: 07-07-2012, 02:43 AM
  3. Data not showing on Pivot Table
    By la_techie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2012, 06:28 PM
  4. pivot table showing zeroes
    By waternut in forum Excel General
    Replies: 6
    Last Post: 01-25-2011, 03:44 PM
  5. Pivot table fields not showing
    By ctenanthe in forum Excel General
    Replies: 2
    Last Post: 09-08-2010, 06:07 PM

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