Use regular Excel pivot table tricks [Slicers and Filters] to create a PowerPivot report showing the top 5 stores for selected divisions.
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.
And I'm going to create a quick little PivotTable 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 as Slicer Horizontal.
Okay, so right now, I get to see 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 said, we want to sort Descending, not based on store name, but based on Sum of Revenue.
Click OK.
All right, so now we get to see, all of the different stores and their revenue.
But this is still a very detailed report.
It has a, you know, 140 different stores.
No one is 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 ten billion dollars.
We can do all of those, but so right here, top five items based on sum of revenue.
Click OK.
And so, we see that Main Place Mall is number 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 number 1 store.
Mission Viejo is now the number 1 store.
Or we can take a look at Luggage and see instantly, which store is the number 1 store for that particular slicer.
So, using the top 10 filter, allows you to create a report of just the top 10 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 5.
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 the grand, if that's going to confuse people, you know if that, 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.
A great tool that's built into all PivotTables.
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.
All right, want to thank you for stopping by.
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.
And I'm going to create a quick little PivotTable 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 as Slicer Horizontal.
Okay, so right now, I get to see 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 said, we want to sort Descending, not based on store name, but based on Sum of Revenue.
Click OK.
All right, so now we get to see, all of the different stores and their revenue.
But this is still a very detailed report.
It has a, you know, 140 different stores.
No one is 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 ten billion dollars.
We can do all of those, but so right here, top five items based on sum of revenue.
Click OK.
And so, we see that Main Place Mall is number 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 number 1 store.
Mission Viejo is now the number 1 store.
Or we can take a look at Luggage and see instantly, which store is the number 1 store for that particular slicer.
So, using the top 10 filter, allows you to create a report of just the top 10 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 5.
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 the grand, if that's going to confuse people, you know if that, 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.
A great tool that's built into all PivotTables.
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.
All right, want to thank you for stopping by.
See you next time, for another netcast from MrExcel.
