Top and Bottom x Items

Quick reference

• 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.