GoSkills
Help Sign up Share
Back to course

Top and Bottom x Items

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

When you want to filter your PivotTable to show the top/bottom x items or top/bottom x% of items in the data set.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Top and Bottom x Items.xlsx
82 KB
Top and Bottom x Items - Completed.xlsx
75.4 KB

Quick reference

Top and Bottom x Items

Display the top/bottom x items/percentage of items in a PivotTable.

Where/when to use the technique

When you want to filter your PivotTable to show the top/bottom x items or top/bottom x% of items in the data set.

Instructions

Accessing the top/bottom x filter area

  • Identify the last item in the rows area of the PivotTable
  • In the field list (not the Rows area) of the Field well, select the item and click the drop-down arrow
  • Choose Values Filters > Top 10

Top x filters

Top x items
  • Set the Top 10 Filter to:
    • Top
    • Set the value of 10 to the number of Items (plus ties) you’d like to show
    • Leave Items as is
    • Choose the field you wish to filter your top x from
Top x %
  • Set the Top 10 Filter to:
    • Top
    • Set the value of 10 to the number of Items (plus ties) you’d like to show
    • Change Items to Percent
    • Choose the field you wish to filter your top x from

Bottom x filters

  • Set the Top 10 Filter the same as the Top x filter (listed above)
  • Change “Top” to “Bottom”
Login to download
  • 00:04 What I want to show you now is not actually something that's really
  • 00:08 a calculation technique as much as it's actually a filtering technique.
  • 00:12 And the whole point behind this is trying to get the top x or
  • 00:16 bottom x items from a particular list.
  • 00:19 In food and beverage, this is particularly important because we always want to know
  • 00:22 what our top selling menu items are.
  • 00:24 When we re-jig our menus, we want to keep those around.
  • 00:27 And we also want to look at what our bottom selling menu items are,
  • 00:30 because when we rejig the menu, we want to get rid of those and make room for
  • 00:33 something else that could hopefully become a top selling item.
  • 00:36 Now when you look at this particular pivot table,
  • 00:38 we've got it set up with the category or
  • 00:40 item names, we've got account of units and account of sales dollars.
  • 00:43 So what I'd like to do is I'd like to figure out a way to say,
  • 00:47 show me only the top five sellers by category.
  • 00:50 And you're going to be amazed, this is actually super, super easy.
  • 00:53 What we're going to do is we're going to to pick on the item name here.
  • 00:56 I'm just going to click on the filter and I'm going to go to Value Filters.
  • 01:00 And down in the bottom you'll notice that we have a Top 10.
  • 01:02 Well that's cool, but I only want five.
  • 01:05 But you'll see that when I actually go and select the Top 10 filter, I can say,
  • 01:10 hey, I really only want this to be the Top 5.
  • 01:13 I can choose by items, I can choose it by percent or sum, I can go by units or
  • 01:18 sales dollars, whichever my value field areas are listed here.
  • 01:22 Now I'm going to go with units, that's going to be good for this one, and
  • 01:26 we'll say OK.
  • 01:26 And at that point you can see the table filters down a little bit.
  • 01:29 And if I go and run account over these guys here, you can see,
  • 01:32 down on the bottom, that I have six items.
  • 01:35 You go, wait, but you asked for five.
  • 01:36 And this is true.
  • 01:38 But what happens is it actually gives me the top five and ties.
  • 01:44 And this is important, because Corona and Coors Light, in this case,
  • 01:49 are actually tied.
  • 01:50 In the case of canned beer,
  • 01:51 I only actually have four items, because there's only four items in the dataset.
  • 01:54 It's not going to invent any new ones, of course.
  • 01:56 Coolers and ciders, there's only three.
  • 01:58 But when we get to draft beer, it's now been cut down to only five units,
  • 02:02 because there were no ties.
  • 02:03 So 36, the pint of Sleeman Honey.
  • 02:05 The next value must be 35 or lower.
  • 02:08 And the same with liquor, we're down to five items, and wine as well.
  • 02:12 Now that's cool, that gives me the top items.
  • 02:15 But what about the bottom items?
  • 02:17 Again, this is actually really, really easy once you know the trick,
  • 02:21 but it's not exactly super intuitive.
  • 02:23 So let's filter this pivot table.
  • 02:24 And what we'll do is we'll go to Value Filters.
  • 02:27 And again, we're going to choose Top 10 to get to our bottom five.
  • 02:33 Once you're in here, one of the other items on the filter is Bottom.
  • 02:38 So we can choose it to be the bottom five items, again, by units.
  • 02:43 And we'll say, OK, and just like that it's filtered again.
  • 02:47 Now one of the things you'll notice that's actually somewhat interesting here is that
  • 02:52 Kokanee is one of our bottom selling items.
  • 02:54 It's also one of our top selling items.
  • 02:56 And the reason for
  • 02:57 this is because there's only actually seven values in this category.
  • 03:00 So therefore when it actually filters them down, there's going to be some overlap.
  • 03:04 So it does make sense.
  • 03:05 But if you look through anything else,
  • 03:07 we can see we only have three items in the Cooler/Cider category.
  • 03:09 So they show up in both places.
  • 03:11 But when we actually start looking at things like liquor, for example, these
  • 03:16 are the bottom five with ties, because they're all listed with a value of one.
  • 03:20 Not a single one of these is on the list that you see in our liquor items here,
  • 03:25 because they all have 11 units.
  • 03:27 So this kind of makes some sense.
  • 03:29 Now what about working with percentages?
  • 03:31 Well we can do that as well.
  • 03:33 It's all done through the exact same place.
  • 03:36 So we'll go through Value Filters, we'll go to Top 10 and
  • 03:41 we're going to say we'd like to see our Top 5% based on our items or units.
  • 03:48 So at this point, when we filter it, there's not very many items in the top 5%.
  • 03:53 So these are going to be our very best sellers.
  • 03:56 What about the bottom five?
  • 03:57 You're going to see a lot more units in this case,
  • 04:00 because there's a lot more that have a quantity of one.
  • 04:02 So in this case, we'll go to Bottom and we'll choose again, the Bottom 5.
  • 04:08 And instead of items, we'll choose percent, by units, and away we go.
  • 04:13 And now as you can see, particularly in liquor, 2 and
  • 04:17 1 units, fall into the 5% range.
  • 04:20 And there we go,
  • 04:21 we've now got a couple of lists that are actually filtered in different ways,
  • 04:25 showing us the top 5 items, top 5% items, bottom 5 items, bottom 5% items.
  • 04:29 So you can see how you can reconfigure this to make this work for
  • 04:32 whatever view you need.

Lesson notes are only available for subscribers.

Percentage Difference From Calculations
04m:10s
Calculated Fields
06m:31s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement