[SOLVED] Top 10 within a drill-down in a pivot table?

[ChrisBusch]

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

[ChrisBusch]

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.

[Peo Sjoblom]

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


Regards,

Peo Sjoblom

"ChrisBusch" wrote:

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

[ChrisBusch]

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?


"Peo Sjoblom" wrote:

> Apply autofilter on the drill down and from the dropdown select Top 10 items
>
>
> Regards,
>
> Peo Sjoblom
>
> "ChrisBusch" wrote:
>
> > how do i apply top 10 within a drill-down in a pivot table?

[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


ChrisBusch wrote:
> 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.
>


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

[ChrisBusch]

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" wrote:

> Double-click on the Vendor field button
> Click the Advanced button
> For Top 10 AutoShow, select On
> Set the Show to Top 10
>
>
> ChrisBusch wrote:
> > 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.
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[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/as...876391033.aspx


ChrisBusch wrote:
> 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" wrote:
>
>
>>Double-click on the Vendor field button
>>Click the Advanced button
>>For Top 10 AutoShow, select On
>>Set the Show to Top 10
>>
>>
>>ChrisBusch wrote:
>>
>>>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.
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


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

[ChrisBusch]

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" wrote:

> 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/as...876391033.aspx
>
>
> ChrisBusch wrote:
> > 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" wrote:
> >
> >
> >>Double-click on the Vendor field button
> >>Click the Advanced button
> >>For Top 10 AutoShow, select On
> >>Set the Show to Top 10
> >>
> >>
> >>ChrisBusch wrote:
> >>
> >>>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.
> >>>
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

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

ChrisBusch wrote:
> 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" wrote:
>
>
>>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/as...876391033.aspx
>>
>>
>>ChrisBusch wrote:
>>
>>>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" wrote:
>>>
>>>
>>>
>>>>Double-click on the Vendor field button
>>>>Click the Advanced button
>>>>For Top 10 AutoShow, select On
>>>>Set the Show to Top 10
>>>>
>>>>
>>>>ChrisBusch wrote:
>>>>
>>>>
>>>>>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.
>>>>>
>>>>
>>>>
>>>>--
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


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