Top 10 within a drill-down in a pivot table?

[Guest]

how do i apply top 10 within a drill-down in a pivot table?

 

[Guest]

For example:

Row Headers are State, then Vendor.
Column Headers are Year.
Cell Value is Sales Amt
Need to display the top 10 vendors by sales amt within each state.

When I use sort and top 10 within the pivot table, i get
top 10 vendors overall sorted within state.

 

[Guest]

Apply autofilter on the drill down and from the dropdown select Top 10 items


Regards,

Peo Sjoblom

 

[Guest]

Thanks, but nothing under the Filter menu item is enabled (including
Autofilter or Advanced Filter) regardless of where I click on the Pivot
Table. Any more suggestions?

 

[Debra Dalgleish]

Double-click on the Vendor field button
Click the Advanced button
For Top 10 AutoShow, select On
Set the Show to Top 10

 

[Guest]

Thanks, but this does not get me what I need.

I need to display the top 10 vendors by sales amt within each state.

When I use sort and top 10 as you describe, i get the top 10 vendors overall
sorted within state.

For example, if none of the vendors for Alabama are in the national top 10,
then Alabama dissapears completely when I turn on AutoShow top 10 as
described.

If Alabama happens to have one or two of the national top 10, then they would
be the only vendors displayed for Alabama.

What I really need is all top 10 for Alabama to be displayed.

 

[Debra Dalgleish]

What I described should give you the top 10 vendors per state in the
pivot table.

Did you click on the Vendor field before you opened the Sort and Top 10
dialog box?

Is the State field to the left of the Vendor field in the row area?

There's an example in the following article that may help you:

http://office.microsoft.com/en-us/assistance/HA010876391033.aspx

 

[Guest]

Thanks again Debra. I'm still not getting what I need.

I was able to do a correct top 10 in another pivot table which was based on
data in a spreadsheet. I'm starting to think it has something to do with PT
functionality when connected to a cube vs. based on spreadsheet data.

I could send you an example spreadsheet with a local cube file if you'd like
to take a look.

 

[Debra Dalgleish]

Sure, send the file and I'll take a look.

Thanks again Debra. I'm still not getting what I need.

I was able to do a correct top 10 in another pivot table which was based on
data in a spreadsheet. I'm starting to think it has something to do with PT
functionality when connected to a cube vs. based on spreadsheet data.

I could send you an example spreadsheet with a local cube file if you'd like
to take a look.


: