How to make a Pivot Table "Top 10" include an "other" category.

**Melvinrobb** · 11-12-2012, 10:39 AM

Hi all
I've created a pivot table, summarizing each salesperson's portfolio.
I have sliders that enables the user to filter the data by
- Salesperson (40 to choose from)
- Manager (5 to choose from)
- Month

Currently I have a Pie chart based on these filters, that displays the "Top 10" customers.
What I would like to do is have the pie chart display the Top 9 customers, and have the rest under an "other" category, rather than only the Top 10. Not sure how easy this is, but i am hoping it can be done.

With no filters applied, the "TOP 10" customers account for 59% of the overall revenue. If i am able to implement this "other" category, the Pie chart could be for 100% of the revenue, and be a more accurate representation.

Thanks in advance.

**cgc17** · 11-12-2012, 11:26 AM

One option is to group all of the 'other' items in the pivot. Select them, right click and click group - then collapse the fields. If you would like to view the detail (items in the group), you can simply expand the new group that is created using the expand/collapse button.

**Melvinrobb** · 11-12-2012, 11:32 AM

I've attached a sample of my scenario.
This essentially will be a dashboard, so i've made it so someone can easily filter and see the portfolio for a particular salesperson, or for a particular manager.
Because I am using slicers, I can't use formulas to determine what should go in the other category, as the filters the user is selecting are not in cells, but are the slicers.
cgc17 - I wouldn't be able to manually do this, as the user will be switching the criteria, thus switching what will be in the "Other" group.

**cgc17** · 11-13-2012, 10:20 PM

edit: I found some pretty good tutorials on slicers. This was my first time seeing them, so I was a bit thrown off at your "magic" at first!

You've gone more advanced than me here, but that is a very cool file. Can you point me in the direction of any resources (books or online) that cover creating dashboards like that?

**Melvinrobb** · 11-14-2012, 10:21 AM

I figured out the solution. It was pretty simple, which I figured it would be, just couldn't think of it at first.
I removed the "top 10" filter that I was using, and simply used formulas to display the first 9 entries in the data, and then sum the remaining customers in the list. Appears to work fine.

Interms of references I would suggest http://chandoo.org/wp/2010/12/08/dyn...ideo-tutorial/

His whole site is pretty useful, but if you search "slicers" he has quite a few articles. I couldn't wrap my head around Pivot Tables a month ago, but the articles on slicers I read was a good incentive to learn.

**cgc17** · 11-14-2012, 09:01 PM

Awesome, thanks Melvinrobb!

**Shibuya89** · 09-09-2014, 05:13 AM

Hi everybody, I see that there wasn't a conversation for a year. Did anyone try to do this by using a tabular model cube as a data source? For example: I have 300+ companies in my data and all data is refreshed based on a time filter. In a pivot chart, I want to display top 10 companies + sum of everything else. However, the top 10 companies aren't the same every month (or year) so it needs to be dynamic.
Does anybody have a solution or an idea on this topic?

**Fotis1991** · 09-09-2014, 05:15 AM

Welcome to the forum. Pls take some minutes to read forum rules because:

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.