Pivot Table Sort and Top 10

[kjclark]

Hello all!

I have a pivot table where I am sorting and showing the top 10 or bottom 10 customers by their margin. Is there anyway to show those customers in the top 10% and bottom 10% of the total margin? I have a calculated field to show margin as a % of Total, but I can't figure out how to show just the top 10%. Hope this makes sense...

Thanks in advance,
Karla

 

[SkipVought]

kj,

Not sure how to do that DIRECTLY in a PivotTable, but I would

1) Calculate the percent
2) Rank by percent
3) Filter the top 10 by rank

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kjclark]

Thanks Skip! Great idea - but this would only give me the top 10 customers ranked by margin % and I am looking for the customers representing the top 10% of margin. Any ideas on this one?

Thanks again!
Karla

 

[SkipVought]

Do you have a column PctOfMargin? Filter Custom >= .9

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kjclark]

Thanks Skip! This might be a remedial question - but how do you filter in a pivot table?

Thanks!
Karla

 

[SkipVought]

Each row/column field has a selector (filter)

If it's a Data field, replicate it in a row outside the picot table and use auto filter on that column



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884