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!

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

http://www.excelforum.com/excel-char...ng-others.html

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.

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 ?

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.

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.

@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.

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.