0

I want to show the top 5 customers by revenue along with the top 5 regions by revenue in one workbook. I want a filter for customers and regions, so I can multi-select which customers / regions will be displayed. I can choose to only displays the top 5 elements ordered by revenue with that same filter.

My problem is: the user can now filter by regions and customers in the filter elements, but the filter elements also do have the option to remove filtering. But this also removes the top 5 clause. I want the top 5 clause to stay all the time (ideally I would also like to offer a top 10 option, but that is a different story). Is this possible?

TvdH
  • 1,088
  • 14
  • 29

1 Answers1

0

I found the answer here:

http://social.msdn.microsoft.com/Forums/en-US/b4392106-f73b-48aa-894b-22f2d291d9cf/excel-2010-slicers-clearfilter-clears-topn-filter-on-a-pivottable?forum=exceldev

To fix, click in the pivot table, right click and choose PivotTable Options. In the Totals & Filters tab, check "Allow multiple filters per field". This was unchecked for me by default. Refresh your PivotTable and reinsert your slicer. This will maintain the Top(N) or Top 10 filter while still allowing you to manipulate the data with the slicer. Clearing the filter through the slicer maintains the Top(N) filter on the PivotTable and any PivotCharts you may have created.

TvdH
  • 1,088
  • 14
  • 29