Problem using 'Top 10 Autoshow' in pivot chart

[Macrobiotic Brown]

Hi everyone - I'm just starting to get my head around the wonderful world of pivot tables and pivot charts, and I'm stuck ...

I've managed to build a pivot chart exactly how I need it to look ... except that there are 140 different data fields showing, and I only want the top 10 values to be displayed. The top 10 would then update depending on the Page Fields selected.

My searching for an answer keeps returning results that say "click Field Settings, then Advanced, then Top 10 Autoshow" etc but the Advanced option doesn't show in the associated Pivot Table - I only have 'OK', 'Cancel', 'Hide', 'Number' & 'Options'.

So is it possible to use Top 10 Autoshow in this case? And how would I go about it?

This is what the pivot chart looks like at the moment:

Any help greatly appreciated!!

 

[DonkeyOte]

You should find there are in effect two pivots - the Chart (as seen) and the Table (on a different sheet)

To implement the Top 10 go to the "other" Pivot - ie the standard Table and apply the Field Setting as described... changing either / or of these "Pivots" will impact the other accordingly.

 

[Macrobiotic Brown]

Thanks DonkeyOte - that's exactly what I thought too, but I can't find any way to apply that field setting in the associated table.

If I right-click the 'Data' field in the table, 'Field Settings' is greyed-out for some reason ... and if I right-click any of the individual data categories, the 'Field Settings' option doesn't contain the 'Advanced' menu I need to access the Top 10 option.

I'm thinking that because the options aren't available then I've set something up wrong in my Pivot Table, but if I move anything then my chart doesn't look how I need it to look.

Is there another way to access the Top 10 option in the Pivot Table?

 

[DonkeyOte]

You won't apply the Top 10 to the Data Fields, you will apply the Top 10 to the Axis Field where the Top 10 is in turn based on the Data Field values.

 

[Macrobiotic Brown]

Right-clicking in the Data Field values gives me the same options (no Advanced or Top 10 options available).

The Pivot Table has two "Page Field" options at the top, then one column of "Data" listing the 140 different fields, and a column right next to that with the total numbers of each. As far as I can see, there's nowhere I can find in the table that will let me access the Top 10 options - even the 'Sort and Top 10' option in the Pivot Table menu is greyed out.

Maybe I'm missing something in the Pivot Table?

 

[Macrobiotic Brown]

Now I'm thinking it could be a problem with the way my source data is set out ... maybe I'll have a play around with that too.

 

[DonkeyOte]

How is the PT Sourced ? (worksheet within file / external source)

Is the Pivot Table sheet Protected ?

Is the Row Field a Calculated Item or is it standard ?

Is the workbook Shared ?

I can't replicate so I'm just trying to pinpoint possibles.

 

[Macrobiotic Brown]

How is the PT Sourced ? (worksheet within file / external source)
Worksheet within file

Is the Pivot Table sheet Protected ?
No

Is the Row Field a Calculated Item or is it standard ?
Errmm ... i think it's standard?

Is the workbook Shared ?
No

I can't replicate so I'm just trying to pinpoint possibles.
Thank you for your patience

Here's some JPEGs showing a mock-up of what I'm trying to do...

The source data:

The Pivot Table:

The Pivot Chart:

The end result I'm after is that the graph would only show the Top 10 values in decending order ('Lollies' thru to 'Cherries').

 

[DonkeyOte]

OK that makes sense - there is no Row Field per se ...
I had assumed Apples / Bananas etc were entries within a given Field / Column rather than being independent columns.

I'm not sure there's a "quick" fix other than modifying your data setup such that apples / bananas are condensed into a single field but separate transactions... eg:

FY | Region | Type | Value
YE Sep09 | North | Apples | 3
YE Sep09 | North | Bananas | 4
etc...

that being said there are undoubtedly workarounds I'm not aware of...

I guess you could apply VBA and iterate the data fields hiding those not in the top 10 - as part of the PT update event... something along the lines of:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim lngRow As Long, lngRowX As Long, lngRank As Long, lngTop As Long, dblTopX As Double, PTF As PivotField
lngTop = 10 'top X - could be made into a cell reference etc...
On Error GoTo ExitPoint
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
With Target
    If .DataPivotField.VisibleItems.Count Then .DataPivotField.Orientation = xlHidden
    For Each PTF In .PivotFields
        If PTF.Orientation = xlHidden Then PTF.Orientation = xlDataField
    Next PTF
    dblTopX = Evaluate("LARGE(" & .DataBodyRange.Address & "," & lngTop & ")")
    For lngRow = .DataBodyRange.Rows.Count To 1 Step -1
        If .DataBodyRange(lngRow).Value < dblTopX Then .DataBodyRange(lngRow).PivotField.Orientation = xlHidden
    Next lngRow
    lngRowX = 1
    For lngRow = .DataBodyRange.Rows.Count To 1 Step -1
        lngRank = WorksheetFunction.Rank(.DataBodyRange(lngRowX),.DataBodyRange)
        If lngRowX = lngRank Then
            lngRowX = lngRowX + 1
        Else
            .DataBodyRange(lngRowX).PivotField.Position = lngRank
        End If
    Next lngRow
End With
ExitPoint:
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

to insert the above, right click on the sheet containing the underlying Pivot Table, select View Code and paste above into resulting window.

(the above's pretty hideous in truth but I'm pretty tired in my defence... hopefully with a new set of eyes a more elegant solution can be found!)

 

[Macrobiotic Brown]

DonkeyOte - thank you so much for your help!! That VBA code looks to be working beautifully.

One question - when I use the code in the sample spreadsheet ("apples", "bananas" etc) it works perfectly, but when I use it in the proper spreadsheet with 140+ different data values, it doesn't sort the results in descending order. Do I need to tweak something in the code to handle a bigger range?