Pivot Slicer Question

[Iain Munro]

Morning

I have a Pivot Table that has 77 rows of clients - after about row 29, the quantity column goes to 1 for the remaining items.

When I created a Pivot Slicer, it would be nice to just to be able to select from say the top 10 clients rather than a slicer that has 77 values to chose feom.

Is there a way to do this ?

Iain

 

[rondeondo]

Hi Iain
I have a way for this but it is a little bit of a hack but it'll work.
In your data create a column that selects the data you want to include in your slicer based on the rank.eq function. This is why it's a bit of a hack as you'll need to calculate your top 10 within the data, maybe using a coutif or sumif depending how you want to define the top 10. So column for your scoring, next column use rank.eq on the previous column and wrap with an if. eg =if(rank.eq(<cell with rank>,<range of cells of rank column>,0)<=10,<Column to show in slicer>,"")
Then use this column as your slicer with the pivot.

Ron

 

[Iain Munro]

Many thanks rondeondo