Slicer for Top 5, 10, 15 items by Sum

[DKolev]

Hi - I've looked for a solution everywhere but couldn't find anything so I am posting here.

I am importing some data from Access to Excel and using a pivot table / chart to summarize it. I'd like to set up the pivot table in a way that allows me to create a slicer for the top 5,10, and 15 items by sum of amounts. Once I set up the fields (rows: item; values: amounts), I know how to change the amounts field settings to that it shows me the rank of the items instead of the actual total amounts. This doesn't help, though. I cannot insert a slicer based on the rank - it always shows the amounts; also - it doesn't let me group the ranked field as it represents values.

Here's how the pivot table is set up (please also see the attached file):

Item Rank Sum of Amount
A 7 296233
B 3 457870
C 2 468153
D 13 168286
E 23 10337
F 21 56430
H 6 301803
I 11 191416
J 15 132950
K 1 632375
L 10 213351
M 19 77899
O 17 90044
P 14 165690
Q 9 225537
R 18 78537
S 12 188442
T 5 334858
V 16 122488
W 22 37713
X 8 228884
Y 4 338451
Z 20 70874

I want a slicer with three slicer items: 5, 10, and 15, representing the top 5,10, and 15 pivot table items based on the sum of amounts. Is this possible with a pivot table? Please see the attached file.

Thanks a lot.

D.

[JosephP]

you can get it with some vba and another pivot table like this

[DKolev]

Thanks - this is a really elegant solution that works great. One follow up question - what if you have an additional field in your data and have created another pivot table out of that field and the amounts (see attached file). Is there a way to apply the ranking (slicer) for the initial field on the additional pivot table? Like, when you click on the 5 top items from the first field, it would automatically update the second pivot table as well, even though the ranking is not based on a field in that table?

[JosephP]

do you mean the second pivot should update to show top 5 based on its amounts?

[DKolev]

No, check the second chart in the attached file from my previous post - i want to filter that you created to be active on it too. Not sure it's possible?

[JosephP]

how do you wanted it filtered? its top 5 items by the amounts in its pivot table or only showing data based on the filter applied to the first pivot table?

[DKolev]

Only showing data based on the filter applied to the first pivot table.

[JosephP]

can you add the row field from the first pivot as a page field in the second to use for filtering?

[DKolev]

I don't know how.

[JosephP]

is this what you want?

[DKolev]

What do you drink?

[JosephP]

too much wine ;-)

[simba01]

This is pretty neat. Worked well for me.

My only issue was response time. I am handling a larger data set and VBA slows down things. Relatively faster via Slicers.

I am thinking -
-Combining couple of pivot table would present an easier solution?
-Moving away from pivot is a much feasible option?
-Why would rank not work (using rank and formula for ranging 0-5, 6-10 etc)

Your comments please Joseph.

Thanks again!

[haotian87]

Can you guys show me how that filter pivot table #1 got linked to the top items filter in pivot table #2?

[wrybel]

Hi,

could you help me add a functionality of sorting in Ascending order for column "Amount". So when I choose any of a slicer pivot table will sort "Amount" in Ascending order.

Please Login or Register  to view this content.

Thanks !

[wrybel]

Hi,

could you help me add a functionality of sorting in Ascending order for column "Amount". So when I choose any of a slicer pivot table will sort "Amount" in Ascending order.

Please Login or Register  to view this content.

Thanks !




----Ok, it can be done simply be setting sorting in pivot table...