PowerPivot enables something called "Asymmetric Reporting". Asymmetric Reporting creates the ability to report Actuals for last year and Budget for this year. Watch as Bill demonstrates this feature of Power Pivot in Episode #1320 today.
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 as actual and it has somewhere down here plan.
All right! So, we have years and actually want to create a regular old pivot table from this Insert, Pivot Table, 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.
All right, 2009, Actual, Plan and then 2009 Total, which adds Actual and Plan that is completely insane, completely mean here is 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 Actual's in 2010, they're all zeros it'll hide the Actual's in 2009.
So, every time I do this I create a Pivot Table, I change the Pivot Table of values, I then have to delete the columns I want horrible.
They added a feature.
Microsoft added the feature in Excel 2010, that allows something called Name Sets.
It's beautiful, it solves this problem here's the thing doesn't work with regular data, that only works with OLAP data.
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 say why would you take this through PowerPivot, the reason I take this through PowerPivot is because PowerPivot creates this data as an OLAP data set.
and then name sets work.
All right, 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 format as a table number, format as a table allows us to create a linked table back here at a power pivot create a linked table.
BAM we're in, create a Pivot Table, Pivot Table, OK.
Now, we're going to the exact same thing we did before we 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.
Okay now, we want to create a name set, we want to created name set.
So, I come over here we'll go to Options, go to Calculation, Fields, Items and we're going to create a set based on column items, column items.
Okay so, what do you 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.
All right and there we have a real live Pivot Table still tied to the original data set, still refreshable it show us 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 Power Pivot, easy to take that regular data, send it through Power Pivot 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.
All right! 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 as actual and it has somewhere down here plan.
All right! So, we have years and actually want to create a regular old pivot table from this Insert, Pivot Table, 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.
All right, 2009, Actual, Plan and then 2009 Total, which adds Actual and Plan that is completely insane, completely mean here is 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 Actual's in 2010, they're all zeros it'll hide the Actual's in 2009.
So, every time I do this I create a Pivot Table, I change the Pivot Table of values, I then have to delete the columns I want horrible.
They added a feature.
Microsoft added the feature in Excel 2010, that allows something called Name Sets.
It's beautiful, it solves this problem here's the thing doesn't work with regular data, that only works with OLAP data.
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 say why would you take this through PowerPivot, the reason I take this through PowerPivot is because PowerPivot creates this data as an OLAP data set.
and then name sets work.
All right, 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 format as a table number, format as a table allows us to create a linked table back here at a power pivot create a linked table.
BAM we're in, create a Pivot Table, Pivot Table, OK.
Now, we're going to the exact same thing we did before we 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.
Okay now, we want to create a name set, we want to created name set.
So, I come over here we'll go to Options, go to Calculation, Fields, Items and we're going to create a set based on column items, column items.
Okay so, what do you 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.
All right and there we have a real live Pivot Table still tied to the original data set, still refreshable it show us 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 Power Pivot, easy to take that regular data, send it through Power Pivot 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.
All right! Want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
