Say that you want to report actuals from last year and budget from this year. Regular pivot tables can't do that. Starting in Excel 2010, OLAP pivot tables can do that. But, that doesn't help you if you have regular old Excel data instead of OLAP cubes.
However...take your regular Excel data through PowerPivot and it turns into an OLAP Cube! Thus, Named Sets become an option.
Chapter 12 makes me want to run every single data set through pivot table. I need to turn back to Chapter 3 to remind myself of why not to do that...
However...take your regular Excel data through PowerPivot and it turns into an OLAP Cube! Thus, Named Sets become an option.
Chapter 12 makes me want to run every single data set through pivot table. I need to turn back to Chapter 3 to remind myself of why not to do that...
Transcript of the video:
MrExcel podcast is sponsored by “Easy-XL”.
PowerPivot for the Data Analyst chapter 12 - Asymmetric Reporting.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Well chapter 12 has a lot of cool stuff, but I chose this one for the podcast, just because it's one that drives me insane.
Regular Pivot tables here, we have Store, Year, Type.
Type is interesting because there’s “Actual”, and it has somewhere down here, “Plan”.
Alright, so we have years, and actually I'm going to create a regular old Pivot table from this, Insert PivotTable, OK.
Let's put Store down the left-hand side, and then Year and Type going across the column labels, and then finally Sales in the heart of the Pivot table.
Alright, 2009, Actual, Plan, and then 2009, Total, which adds Actual and Plan, that is completely insane, completely horrible! 2010, Actual and Plan, and then 2010 Total.
What I really, really want to do here is, I want 2009 Actual, 2010 Plan.
No way to do it, not in a regular Pivot table, because if I hide the actuals in 2010, they're all zeros, it'll hide the actuals in 2009.
So, every time I do this, I create a Pivot table, I change the Pivot table to values, I then have to delete the columns I want.
Horrible! Microsoft added a feature in Excel 2010 that allows something called Name Sets.
It's beautiful, it solves this problem, and here's the thing, it doesn't work with regular data, it only works with OLAP data.
I hate that! Hey, here's an amazing thing, this is just a boring, really small data set, not even 400 rows, there's nothing to join.
You would say “Why would you take this through PowerPivot?” The reason I'd take this through PowerPivot is because PowerPivot creates this data as an OLAP dataset, and then name sets work.
Alright, I realize this is complete overkill, it's like using a huge nuclear submarine to kill an ant, but it kills the ant, it solves the problem.
So, let's check it out.
We're going to go to insert Format as Table, remember, Format as Table allows us to create a linked table.
Back here to PowerPivot, create a linked table, BAM, we're in! Create a PivotTable, PivotTable, OK.
Now we're going to do the exact same thing we did before, we're going to put stores down the left hand side, sales in the heart of the pivot table, and then year and type in the top.
OK, now we want to create a named set.
So I come over here, we'll go to Options, go to Calculations, “Fields, Items”, and we're going to Create a Set Based on Column Items.
Ok, so what do we want?
2009, Actual, that's good, I don't want 2009, Plan, so we choose that row, delete that row.
Don't want that silly Total, it's horrible! Who would even put that in?
2010, Actual, don't have that yet, 2010, Plan, perfect 2010, delete, and then All delete, click OK.
Alright, and there we have it, a real live Pivot table, still tied to the original data set, still refreshable, it shows 2009, Actual, 2010, Plan using name sets.
What a great feature that Microsoft added to Excel 2010, but to not allow it for regular Pivot tables, only for OLAP Pivot tables, very frustrating.
Good news, if you have PowerPivot, easy to take that regular data, send it through PowerPivot, back to Excel, and you can now create name sets.
This is called an asymmetric report, thrilled that it is finally viable here in Excel 2010.
Alright hey, 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 12 - Asymmetric Reporting.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Well chapter 12 has a lot of cool stuff, but I chose this one for the podcast, just because it's one that drives me insane.
Regular Pivot tables here, we have Store, Year, Type.
Type is interesting because there’s “Actual”, and it has somewhere down here, “Plan”.
Alright, so we have years, and actually I'm going to create a regular old Pivot table from this, Insert PivotTable, OK.
Let's put Store down the left-hand side, and then Year and Type going across the column labels, and then finally Sales in the heart of the Pivot table.
Alright, 2009, Actual, Plan, and then 2009, Total, which adds Actual and Plan, that is completely insane, completely horrible! 2010, Actual and Plan, and then 2010 Total.
What I really, really want to do here is, I want 2009 Actual, 2010 Plan.
No way to do it, not in a regular Pivot table, because if I hide the actuals in 2010, they're all zeros, it'll hide the actuals in 2009.
So, every time I do this, I create a Pivot table, I change the Pivot table to values, I then have to delete the columns I want.
Horrible! Microsoft added a feature in Excel 2010 that allows something called Name Sets.
It's beautiful, it solves this problem, and here's the thing, it doesn't work with regular data, it only works with OLAP data.
I hate that! Hey, here's an amazing thing, this is just a boring, really small data set, not even 400 rows, there's nothing to join.
You would say “Why would you take this through PowerPivot?” The reason I'd take this through PowerPivot is because PowerPivot creates this data as an OLAP dataset, and then name sets work.
Alright, I realize this is complete overkill, it's like using a huge nuclear submarine to kill an ant, but it kills the ant, it solves the problem.
So, let's check it out.
We're going to go to insert Format as Table, remember, Format as Table allows us to create a linked table.
Back here to PowerPivot, create a linked table, BAM, we're in! Create a PivotTable, PivotTable, OK.
Now we're going to do the exact same thing we did before, we're going to put stores down the left hand side, sales in the heart of the pivot table, and then year and type in the top.
OK, now we want to create a named set.
So I come over here, we'll go to Options, go to Calculations, “Fields, Items”, and we're going to Create a Set Based on Column Items.
Ok, so what do we want?
2009, Actual, that's good, I don't want 2009, Plan, so we choose that row, delete that row.
Don't want that silly Total, it's horrible! Who would even put that in?
2010, Actual, don't have that yet, 2010, Plan, perfect 2010, delete, and then All delete, click OK.
Alright, and there we have it, a real live Pivot table, still tied to the original data set, still refreshable, it shows 2009, Actual, 2010, Plan using name sets.
What a great feature that Microsoft added to Excel 2010, but to not allow it for regular Pivot tables, only for OLAP Pivot tables, very frustrating.
Good news, if you have PowerPivot, easy to take that regular data, send it through PowerPivot, back to Excel, and you can now create name sets.
This is called an asymmetric report, thrilled that it is finally viable here in Excel 2010.
Alright hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
