Create 2009 Actual vs 2010 Budget in a pivot table.
Using PowerPivot for Excel 2010 to create asymmetric pivot tables.
Using PowerPivot for Excel 2010 to create asymmetric pivot tables.
Transcript of the video:
MrExcel podcast is brought to you by ”Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I'm having a tough time today, it’s PowerPivot, not that thing that you do presentations in.
I have Zeke over here, and Zeke gets a dollar every time I say the wrong word, and he's going to throw stuff at me, so it’s PowerPivot.
Alright hey, I've got an amazing trick for you today.
Now PowerPivot we've talked about before, PowerPivot is that product that can take millions of rows of data, and mash it up with other data, and create Pivot tables.
I was working on a book last week, and I realized that PowerPivot can do something amazing, that's not really what it's advertised for.
Check this out, we're going to go to Insert PivotTable, click OK, now I'm going to create a Pivot table here, where I have Year and Measure across the top, maybe Regions down the side, and then Amount.
Alright now, 2009 Actuals, those are real, 2009 Budget I don't care anymore, and in 2010 I don't want the Actuals, I just want the Budget, because it's not a full year for 2010.
But there's no way to do this in a regular Pivot Table, there's no way to- if you turn off Budget for 2009, it's going to turn off for 2010 as well.
Alright so, kind of have some good news, bad news, good news, kind of thing.
In Excel 2010 they added something called Name Sets that allows you to do that asymmetrical reporting, but it doesn't work for regular Excel data sets, it only works for OLAP data sets.
Which for those of us who have our data in Excel, we don't have a big IT department behind us, does us no good!
Alright, check this out, I want to press Ctrl+T to create a table, I'll come up here and name this table, we’ll call it Financials, and now watch this little dance.
I'm going to go to PowerPivot, Create Linked Table, allow it to import that data, and I'm not going to mash-up any data, I'm not going to do any calculations here.
All I'm going to do is PivotTable, Single PivotTable, New Worksheet.
OK, so see that?
I took a regular Excel data, made it into a table, had PowerPivot import it, and now, alright, let’s build that same report again.
So we have Year in column labels, Measure in column labels reaching down the side, and Amount in the heart of the Pivot table.
Right, same problem we had before, ah!
But every PowerPivot Pivot table is not a regular Pivot table, It’s an OLAP Pivot table.
So if I come here to Options, Field Sets, Create a Set based on Column Items!
Alright, so 2009 Actual, yes I want that, 2009 Budget, no, get rid of it.
2009 All, don't need it, that’s the total.
2010 Actual, nope, not yet, 2010 Budget, yep, want to keep that, don’t need this one, and don't need this one.
We’ll call it instead of Set1, maybe BudVAct, click OK!
Alright, and check this out over here, we now have an extra virtual field called BudVAct, it actually replaced Year and Measure with BudVAct, and 2009 Actual, 2010 Budget.
Asymmetric Reporting, a HUGE underutilization of PowerPivot, because you know, we're not mashing up millions of rows of data.
But a great way to get access to name sets, even though Microsoft is not giving us access to name sets for regular Excel data.
Hey, there you have it, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I'm having a tough time today, it’s PowerPivot, not that thing that you do presentations in.
I have Zeke over here, and Zeke gets a dollar every time I say the wrong word, and he's going to throw stuff at me, so it’s PowerPivot.
Alright hey, I've got an amazing trick for you today.
Now PowerPivot we've talked about before, PowerPivot is that product that can take millions of rows of data, and mash it up with other data, and create Pivot tables.
I was working on a book last week, and I realized that PowerPivot can do something amazing, that's not really what it's advertised for.
Check this out, we're going to go to Insert PivotTable, click OK, now I'm going to create a Pivot table here, where I have Year and Measure across the top, maybe Regions down the side, and then Amount.
Alright now, 2009 Actuals, those are real, 2009 Budget I don't care anymore, and in 2010 I don't want the Actuals, I just want the Budget, because it's not a full year for 2010.
But there's no way to do this in a regular Pivot Table, there's no way to- if you turn off Budget for 2009, it's going to turn off for 2010 as well.
Alright so, kind of have some good news, bad news, good news, kind of thing.
In Excel 2010 they added something called Name Sets that allows you to do that asymmetrical reporting, but it doesn't work for regular Excel data sets, it only works for OLAP data sets.
Which for those of us who have our data in Excel, we don't have a big IT department behind us, does us no good!
Alright, check this out, I want to press Ctrl+T to create a table, I'll come up here and name this table, we’ll call it Financials, and now watch this little dance.
I'm going to go to PowerPivot, Create Linked Table, allow it to import that data, and I'm not going to mash-up any data, I'm not going to do any calculations here.
All I'm going to do is PivotTable, Single PivotTable, New Worksheet.
OK, so see that?
I took a regular Excel data, made it into a table, had PowerPivot import it, and now, alright, let’s build that same report again.
So we have Year in column labels, Measure in column labels reaching down the side, and Amount in the heart of the Pivot table.
Right, same problem we had before, ah!
But every PowerPivot Pivot table is not a regular Pivot table, It’s an OLAP Pivot table.
So if I come here to Options, Field Sets, Create a Set based on Column Items!
Alright, so 2009 Actual, yes I want that, 2009 Budget, no, get rid of it.
2009 All, don't need it, that’s the total.
2010 Actual, nope, not yet, 2010 Budget, yep, want to keep that, don’t need this one, and don't need this one.
We’ll call it instead of Set1, maybe BudVAct, click OK!
Alright, and check this out over here, we now have an extra virtual field called BudVAct, it actually replaced Year and Measure with BudVAct, and 2009 Actual, 2010 Budget.
Asymmetric Reporting, a HUGE underutilization of PowerPivot, because you know, we're not mashing up millions of rows of data.
But a great way to get access to name sets, even though Microsoft is not giving us access to name sets for regular Excel data.
Hey, there you have it, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
