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

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

Post by Q2hyaXNCdX » Fri, 03 Jun 2005 00:23:37


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

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

Post by Q2hyaXNCdX » Fri, 03 Jun 2005 01:00:09

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.

 
 
 

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

Post by UGVvIFNqb2 » Fri, 03 Jun 2005 01:03:36

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


Regards,

Peo Sjoblom
 
 
 

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

Post by Q2hyaXNCdX » Fri, 03 Jun 2005 02:38:05

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?
 
 
 

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

Post by Debra Dalg » Fri, 03 Jun 2005 06:46:29

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





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.yqcomputer.com/
 
 
 

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

Post by Q2hyaXNCdX » Fri, 03 Jun 2005 11:03:53

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.
 
 
 

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

Post by Debra Dalg » Fri, 03 Jun 2005 11:57:30

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://www.yqcomputer.com/







--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.yqcomputer.com/
 
 
 

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

Post by Q2hyaXNCdX » Sat, 04 Jun 2005 01:08:04

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.
 
 
 

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

Post by Debra Dalg » Sat, 04 Jun 2005 04:36:57

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








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.yqcomputer.com/