Pivot table - show top 20 and group remaining data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way of showing the top 20 entries in a pivot table, then
grouping the remaining entries as one, i.e. 'Other'.
 
You need to provide more information about the fields in your PivotTable. The
Top 10 filter (which can be anything like Top 5, Top 20) can only be applied
a single field in your Values report area.

To find the Top 20 in all of the Values report area of your PivotTable,
apply Conditional Formatting to highlight the cells.

To get rid of the "Other" data depends if it is in one field or many fields
in your PivotTable. You can use a calculated field to add them, count them
etc. and name it Other and then remove the "Other" fields from the PivotTable.

Again, without informaton about what fields and their location in a
PivotTable report area, it is difficult to answer your question completely.
 
To do what you are asking will require you to add a field to yoru source
data. By using array, sumproduct and the rank/large functions you should be
able to identify the top 20 and the others...
 
The left hand pane of my pivot table shows a list of company names. The
corresponding column shows the associated amounts, by company. 'Sum of
Amount' is in cell A9.

I'd like to be able to show the Top x number of rows, say for this example,
10 rows (so up to and including Company J). I'd like to group the remaining
rows (Company K to Company T inclusive) as 'Other' and sum the amount for
that group. As a result, I'd like to create a graph that identifies the
market share by the top 10 companies, but also including one chunk which
represents the amount of business placed by 'other'.

The data for this pivot table will change on a monthly basis, therefore the
top 10 companies may differ each time, therefore I don't think its just a
case selecting the top 10 companies and grouping that list.

Sum of Amount pt_class_code
Motor
company_name New Business
Company A £100,000
Company B £90,000
Company C £80,000
Company D £70,000
Company E £60,000
Company F £50,000
Company G £40,000
Company H £30,000
Company I £20,000
Company J £19,000
Company K £18,000
Company L £17,000
Company M £16,000
Company N £15,000
Company O £14,000
Company P £13,000
Company Q £12,000
Company R £11,000
Company S £10,000
Company T £9,000
Grand Total £694,000

I hope that makes sense. I didn't want to makes things too complicated.
 
Hi Sarah

You can't do it totally automatically, but there would be very little to do
each month to give your required result.
To get the top 20
double click on the Company field>Advanced>Autosort>Descending>Using
field>Sum of value>OK>OK

This will give your companies in descending order of value

You can highlight any company names below the top 20>right click>Group and
Show Detail>Group
This will allocate the name Group1 which you can overtype with whatever name
you wish.
Double click the Group title, and the rows will be filtered to show the
total value for the Group below the lowest of your top 20 companies.

If you want to Ungroup them, repeat the procedure but choose Ungroup.

You will need to Ungroup each Month, do a Refresh, then Group those values
below the top 20 once again.
 
Back
Top