Pivot Table Filtering

[seguin85]

Hi,

I have a file that is using pivot tables and slicers to output pie charts for another team in our company to use. Is there a way to show the top ten customers on the chart and then all other customers by "Other". They insist on using the 3D charts as that is what they have used in the past. Any help would be appreciated.

 

[SpillerBD]

Right click the Customer Label in the Pivot Table. Select Filter "Top 10" Set to the desired values (Top, 10, by which value)

 

[seguin85]

I have done that, but it doesn't show the "Other" group. It only show the top 10, so now some information is missing.

 

[SpillerBD]

Oh, like how grouping will group the specified items and then aggregate the remaining...
I'm not sure. I'll punt for now.

 

[seguin85]

ADVERTISEMENT

Precisely, something like:

cust1	500
cust4	480
cust8	440
cust9	410
cust12	400
cust2	350
cust10	305
cust3	280
cust15	220
cust11	200
other	800

<tbody>
</tbody>

And then if I click a slicer (for example by month), it will have a new top 10 and then the new other value.
Thanks

 

[SpillerBD]

I get there a hard way.
Turn off the Top 10 filter.
Sort the labels by the Value and then select the values that should be in the "Other" category. Right Click and select Group. You'll get a "Group1" which you can rename to "Other" or what ever may be appropriate.

I don't believe this will auto-update properly and would imagine there is an easier way of getting to the end result without these steps and a solution that just updates as desired. I hope someone can have a suggestion for that.

 

[seguin85]

I try and play around with that idea and see if I can manipulate it a bit to fit my needs.