Top and Bottom x Items

Quick reference

• 00:04 In this video we're gonna look at another really cool feature with pivot tables,
• 00:10 and that's how to filter them to only show the top 10% or 5% or 10 items.
• 00:16 This is really important in retail because you've got a whole bunch of sales items
• 00:20 and you want to know, not only what your top sellers are,
• 00:22 because those are the ones that you really want to promote a little bit, but
• 00:26 you also want to know what your bottom items are.
• 00:28 That way you know which things you should actually be taking off your menus or
• 00:31 removing from your stores to try and make way for
• 00:34 new products that you hope will bubble up to be your top sellers.
• 00:37 So when we look at the particular pivot tables here,
• 00:41 I've got two pivot tables set up.
• 00:42 And you can see that we've got categories down the left and
• 00:44 we've got our sales items.
• 00:46 We've also got a sum of units and
• 00:48 a sum of sales dollars that we actually have going in here.
• 00:51 Now what I want to do is I want to filter this to show my top
• 00:55 five sales items by category.
• 00:58 That's where I'm gonna start, and you're going to find this is just amazingly easy.
• 01:02 What we do is when we've got things on the pivot table, we've got a couple ways we
• 01:06 can do this, but the easiest way I find is to click on the little filter arrow,
• 01:10 which is by the last item in the row category here.
• 01:15 So, we're gonna just left click on that rather, and go to Value Filters, and
• 01:20 down at the bottom you can see Top 10.
• 01:21 And when we go into Top 10 it says would you like to filter by the top.
• 01:26 Sure, how many would you like?
• 01:28 I'm gonna take five items, yeah that works, and by units.
• 01:32 Now, I can choose by units or sales dollars.
• 01:35 I'm going to go with units to start with here, and we'll say OK.
• 01:39 You'll notice that there's been a little bit of movement that's been going on in
• 01:42 this particular table.
• 01:43 I should now see that each of my categories has a maximum of five
• 01:48 different sales items, although you will occasionally see one or
• 01:52 two more because it gives you the top five with ties.
• 01:57 So if we have a whole bunch of items, for example, in this area,
• 02:00 if I had another item that had 16 units,
• 02:02 I would actually end up having six items in my bottled beer.
• 02:06 Okay? Because it would actually rank the top
• 02:08 five being one, two, three, four, five, and if there were two units of 16,
• 02:13 it would put both of them on because it can't determine which belong there, so
• 02:16 it's top x plus ties.
• 02:19 Now you'll notice that trying to come up with the other side of things,
• 02:23 where we want our bottom units, is actually amazingly easy as well.
• 02:28 Again, we go and we click on little filter icon, we go to Value Filters, and again,
• 02:34 and this is not intuitive, we go to top ten, but we change from top to bottom.
• 02:41 We change our number from ten to five,
• 02:47 and we're gonna leave this with items and units, and away we go.
• 02:51 And you can now see that our top selling units in bottled beer,
• 02:55 Budweiser and Heineken.
• 02:57 Kokanee actually also happens to be one of our bottom selling units, so
• 03:00 it looks like we don't have a ton of different sales items in here.
• 03:02 However, when we go down and we look at things like liquor, you can see our top
• 03:07 sellers, we've got items from Ceasars all the way down to Drambuie 11 units.
• 03:12 When we look at our liquor here, I've got a lot more than
• 03:15 five items that's only sold one unit each, but this is what I say.
• 03:19 We've got our bottom five in ties, so, it's gonna give us at least five different
• 03:23 items in this, but if there's a similar value,
• 03:26 it's gonna repeat it across the board for all of those guys.
• 03:29 So, we now know which items we should possibly be moving on, and our wines,
• 03:33 doesn't look like these ones have done so well.
• 03:35 Whereas these guys over here are doing really well, so
• 03:38 we want to make sure that we keep promoting them.
• 03:40 Now that's great but what if we want to do the top x percent,
• 03:45 the top 5 percent for example.
• 03:47 Well guess what?
• 03:48 Same place, these pivot tables are exactly the same built off the same information.
• 03:53 Once again we'll go and we'll click and we'll say Value Filters Top ten,
• 03:58 and we'll go with Top.
• 04:01 We're gonna change to five, but this time, instead of items,
• 04:05 we'll say percent by units, and OK, and it knocks us down to the top five percent.
• 04:12 And obviously, it's a very short list, and that's fine, we're okay with that.
• 04:16 Let's take a look at what the bottom 5% gives us.
• 04:18 Value filters, top 10, we'll go with bottom,
• 04:23 5 and percent by units.
• 04:31 And you can see we get a lot longer list because there are a huge amount of items
• 04:35 that only sold one or two units.
• 04:37 So in the bottom five when we actually scan the entire list of all of the units
• 04:42 that we've actually summed up, it looks like anything with ten units or
• 04:46 below is going to end up showing in this list.That's
• 04:48 how these particular guys work.
• 04:51 There's one other place that we could actually do this, and
• 04:54 that is by filtering from the little icon over here.
• 04:56 When we actually come over to our field and you see this little filter icon,
• 05:00 you can again get to your value filters in the same way.
• 05:03 Personally I find it easier to do it by the pivot table on the side here because
• 05:06 it's a little bit closer to the work surface that I'm actually working with.

Lesson notes are only available for subscribers.