Use regular Excel pivot table tricks to create a report showing the top 5 stores for selected divisions.
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
Transcript of the video:
MrExcel podcast is sponsored by “Easy-XL”!
PowerPivot for the Data Analyst chapter 8 - Top 10 Report!
Well, we've been working with this data set, I'm going to create a quick little Pivot table here, Insert PivotTable, and we'll put store name down the left-hand side, revenue in the heart of the Pivot table.
So now we have a list of all of our stores, and I'd like to be able to add a slicer here.
So I'm going to take the Division field, and add that to Slicers Horizontal, OK, so right now I get to see the sales by store for all of the different divisions.
Now I'm going to show you a cool trick here.
If I want to have these stores arranged highest to lowest based on revenue, we come into Row Labels, More Sort Options, and say that we want to sort descending, not based on store name, but based on some of revenue, click OK!
So now we get to see all of the different stores and their revenue, but this is still a very detailed report, it has, you know, 140 different stores, no one's really going to look at this, so.
We're going to use a very cool trick here, we're going to open this drop-down again, and go to Value Filters, and choose Top 10.
Now, even though it's called Top 10, it isn't necessarily 10, it can be the top or bottom, and we can choose how many items.
So I'm going to choose the Top 5 stores, notice it can be the number of stores or, if we wanted, the Top 80%, or enough stores to get us 10 billion dollars, we can do all of those.
So right here, Top 5 items based on sum revenue, click OK, and so we see that Main Place Mall is #1 for all items, but now here's the really cool thing where the slicers come in.
So let's say that we're just interested in shoe sales, so I choose shoes from the slicers, and Main Place is not the #1 store, Mission Viejo is now the #1 store.
Or we can take a look at luggage, and see instantly which store is the #1 store for that particular slicer.
So using the Top 10 filter allows you to create a report of just the Top N items very, very easily.
Now you know, the one thing that you have to be careful about here, the grand total is the grand total of just those five.
So if you wanted to have a line that said “All Other”, that's a completely different problem, so just be aware of it, that's going to confuse people.
If that's going to be a problem, just come back here to the Design tab, Grand Totals, and say “Off for Rows and Columns”, and now we just get a nice little report, Top 5, and we're good to go.
Choose an item from the slicer, get to see the Top 5 stores for that particular item.
Great tool that's built into all Pivot tables, works really well, once we have this data from PowerPivot and a slicer, almost kind of dashboard like now, being able to see the Top 5.
Alright, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
PowerPivot for the Data Analyst chapter 8 - Top 10 Report!
Well, we've been working with this data set, I'm going to create a quick little Pivot table here, Insert PivotTable, and we'll put store name down the left-hand side, revenue in the heart of the Pivot table.
So now we have a list of all of our stores, and I'd like to be able to add a slicer here.
So I'm going to take the Division field, and add that to Slicers Horizontal, OK, so right now I get to see the sales by store for all of the different divisions.
Now I'm going to show you a cool trick here.
If I want to have these stores arranged highest to lowest based on revenue, we come into Row Labels, More Sort Options, and say that we want to sort descending, not based on store name, but based on some of revenue, click OK!
So now we get to see all of the different stores and their revenue, but this is still a very detailed report, it has, you know, 140 different stores, no one's really going to look at this, so.
We're going to use a very cool trick here, we're going to open this drop-down again, and go to Value Filters, and choose Top 10.
Now, even though it's called Top 10, it isn't necessarily 10, it can be the top or bottom, and we can choose how many items.
So I'm going to choose the Top 5 stores, notice it can be the number of stores or, if we wanted, the Top 80%, or enough stores to get us 10 billion dollars, we can do all of those.
So right here, Top 5 items based on sum revenue, click OK, and so we see that Main Place Mall is #1 for all items, but now here's the really cool thing where the slicers come in.
So let's say that we're just interested in shoe sales, so I choose shoes from the slicers, and Main Place is not the #1 store, Mission Viejo is now the #1 store.
Or we can take a look at luggage, and see instantly which store is the #1 store for that particular slicer.
So using the Top 10 filter allows you to create a report of just the Top N items very, very easily.
Now you know, the one thing that you have to be careful about here, the grand total is the grand total of just those five.
So if you wanted to have a line that said “All Other”, that's a completely different problem, so just be aware of it, that's going to confuse people.
If that's going to be a problem, just come back here to the Design tab, Grand Totals, and say “Off for Rows and Columns”, and now we just get a nice little report, Top 5, and we're good to go.
Choose an item from the slicer, get to see the Top 5 stores for that particular item.
Great tool that's built into all Pivot tables, works really well, once we have this data from PowerPivot and a slicer, almost kind of dashboard like now, being able to see the Top 5.
Alright, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
