Another Dueling Excel Podcast with Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen. Today, in Episode #1404, Mike and Bill look at finding the Percent of the Parent Row Total in Excel 2003 to 2010 using Formulas and/or PowerPivot.
Transcript of the video:
Hey! Welcome back, it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.com and Mike Girvin the real Excel Is Fun, not that joker's out there trying to steal, Mike's identity.
Today, podcast episode, dueling Excel podcast, episode 79.
Percentage of Parent.
All right! There's gonna be a three-part.
I have two different ways to do this, we're trying to come up with a pivot table that shows Dallas.
The Dallas market as a percentage of the central regions.
Let's first start with a pivot table, insert pivot table.
I'm going to go there, an existing worksheet right there, bam!
Click [ OK ], we want along the left-hand side, we want region and then market and I want to see total revenue, okay.
I want to come up with a percentage of Chicago divided by Central region.
Chicago divided by Central region, and this is brand-new in Excel 2010, a really really easy way to do this.
I drag it down there, a second time.
Go to the second version, let's scroll over here a little bit and our pivot table tools, let's see other calculations.
Show values as new, in Excel 2010.
Percentage of parent row, total.
All right! Now, this is a lot easier than the old way.
Although you know, I understand that 35.57 is the east percentage of the grand total.
I get that I just, I never feels right to me to see those numbers there but anyway, that's the Excel 2010 way like.
Let's see the old way, the way that we do back in Excel 2007 and Excel 2003.
Mike: Thanks, MrExcel.
Wow! That pivot table, that percentage of parent, new calculation.
I love it.
I use it in statistics, all time.
On an earlier version, so we had to get a little tricky here.
Here's our data set, I'm just going to add an extra column, percent of parent.
Can't text, oh well.
Now, over in this pivot table right here.
We already have the sum, right here for Baltimore, in the east or Chicago in the central and then we simply to get this 16 percent.
We say hey, this divided by the total but over here, what if we said this, equals the actual little bitty revenue.
This hasn't been summarized, yet.
SUMIF, the SUMIF, what remember Dallas, we need the total for Dallas and then compare it the total for the central region.
Well, we can add up all the central region.
We could take the range, here's the criteria [ ctrl shift down arrow ], [ F4 ] comma.
This right here, so as we go down this will be locked, this one.
So, as we move down, we'll always get the total, comma, the sum range.
Well, that's going to be this right here.
Now, what is this going to do?
This is going to be an actual small percentage but all of the Dallas as if we were to add them up.
Where's another Dallas?
Right here, so there's...
We have a little 3% here for Dallas and then we also have another one here, a little 2%.
Well, if we add all those up, we get our percent of total.
So, now I'm going to create a pivot table, [ alt N V T ].
That's the keyboard shortcut in 2007 and 10, existing worksheet.
I'm going to click right here and click [ OK ], all right!
I'm going to take the region down here the market right down here.
So now, we have that little right there and I'm just going to add drop the percent of total right there and boom!
The default is sum and sure enough, that gets our percent.
We could right-click, value field settings, number, we always want to do it this way because it sticks when you pivot it, and then I'm going to say percentage.
Click [ ok ], click [ ok ] and there we have it, not showing the 30 or whatever we had over here.
Remember, we head down there for but there you go, we have the same actual percentages right here.
Right, throw it back to MrExcel.
MrExcel: Hey! Mike, that is cool.
I love this formula.
Although, you know I've used that in the past, not many times.
It's horrible when you get a hundred thousand rows it just takes forever.
Let's try a power pivot solution to this.
So, I have my original data set, create a linked table in the Power pivot tab.
Click [ ok ].
All right! There's my data.
I'll take this back as a pivot table, new worksheet, that's great.
Let's put region, markets and then revenue.
All right! Now, here's where the power of power pivot comes in, take a look at this cell.
How many filters are applied to that cell?
There's actually, two filters.
The first filter says hey, the market has to be Chicago and the second filter says the region has to be central.
We're going to do a calculation here.
Let's a new measure and the calculation is going to be equal calculate.
Calculate is kind of like SUMIF.
I want the sum of table 1, revenue and then here's the filter.
Now, normally in a sum if you apply filters to the data, here I'm actually going to remove a filter.
I want to say, I want to apply all the filters that are naturally upon that cell of the pivot table.
So, this cell has two filters, it has market equal Chicago, region, equal central, but in this case, I'm going to say well, wait for the market.
I don't want any filter, all I want all of the markets.
All of the markets, so all table 1 markets close the...
Calculate, now these formulas are so new and different.
I always check the formula and nowhere is information.
You have to click check from them and hold down check formula.
Otherwise the tool tip covers up the result and what are we going to call this we're going to call this revenue, for region, click [ ok ].
Alright! And check that out.
Now, it's only doing calculations here, 15 calculations.
So, if you had out of thousand rows, it wouldn't have to do 100,000 calculations and add them up.
It's literally only doing this 15 and the beautiful thing here is that, for Chicago is showing us the total revenue for the Central region.
All right! So, a very fast way to do that.
Now, that we have that measured, let's create one new measure, and we call this a percent of region and the answer is going to be...
Alright! So, we have our existing table 1 revenue that has to be, equals sum of table 1 revenue divided by that measure table 1.
I can just put the measure name in there, revenues and region, parenthesis look okay, check formula and that looks good.
Let's click [ ok ], cross our fingers.
There we go, nice.
Now, we need to format that steps back on the pivot table field settings, number formats, percentage, let's go with one, click [ OK ], click [ ok ] and the same answer that Mike came up with but with a large data set.
You know, even with in Power pivot 100 million rows, this is going to be very very quick because they only have to do this calculation 15 times this calculation, 15 times and we're done.
Hey! I want to thank everyone for stopping by, we'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel.com and Mike Girvin the real Excel Is Fun, not that joker's out there trying to steal, Mike's identity.
Today, podcast episode, dueling Excel podcast, episode 79.
Percentage of Parent.
All right! There's gonna be a three-part.
I have two different ways to do this, we're trying to come up with a pivot table that shows Dallas.
The Dallas market as a percentage of the central regions.
Let's first start with a pivot table, insert pivot table.
I'm going to go there, an existing worksheet right there, bam!
Click [ OK ], we want along the left-hand side, we want region and then market and I want to see total revenue, okay.
I want to come up with a percentage of Chicago divided by Central region.
Chicago divided by Central region, and this is brand-new in Excel 2010, a really really easy way to do this.
I drag it down there, a second time.
Go to the second version, let's scroll over here a little bit and our pivot table tools, let's see other calculations.
Show values as new, in Excel 2010.
Percentage of parent row, total.
All right! Now, this is a lot easier than the old way.
Although you know, I understand that 35.57 is the east percentage of the grand total.
I get that I just, I never feels right to me to see those numbers there but anyway, that's the Excel 2010 way like.
Let's see the old way, the way that we do back in Excel 2007 and Excel 2003.
Mike: Thanks, MrExcel.
Wow! That pivot table, that percentage of parent, new calculation.
I love it.
I use it in statistics, all time.
On an earlier version, so we had to get a little tricky here.
Here's our data set, I'm just going to add an extra column, percent of parent.
Can't text, oh well.
Now, over in this pivot table right here.
We already have the sum, right here for Baltimore, in the east or Chicago in the central and then we simply to get this 16 percent.
We say hey, this divided by the total but over here, what if we said this, equals the actual little bitty revenue.
This hasn't been summarized, yet.
SUMIF, the SUMIF, what remember Dallas, we need the total for Dallas and then compare it the total for the central region.
Well, we can add up all the central region.
We could take the range, here's the criteria [ ctrl shift down arrow ], [ F4 ] comma.
This right here, so as we go down this will be locked, this one.
So, as we move down, we'll always get the total, comma, the sum range.
Well, that's going to be this right here.
Now, what is this going to do?
This is going to be an actual small percentage but all of the Dallas as if we were to add them up.
Where's another Dallas?
Right here, so there's...
We have a little 3% here for Dallas and then we also have another one here, a little 2%.
Well, if we add all those up, we get our percent of total.
So, now I'm going to create a pivot table, [ alt N V T ].
That's the keyboard shortcut in 2007 and 10, existing worksheet.
I'm going to click right here and click [ OK ], all right!
I'm going to take the region down here the market right down here.
So now, we have that little right there and I'm just going to add drop the percent of total right there and boom!
The default is sum and sure enough, that gets our percent.
We could right-click, value field settings, number, we always want to do it this way because it sticks when you pivot it, and then I'm going to say percentage.
Click [ ok ], click [ ok ] and there we have it, not showing the 30 or whatever we had over here.
Remember, we head down there for but there you go, we have the same actual percentages right here.
Right, throw it back to MrExcel.
MrExcel: Hey! Mike, that is cool.
I love this formula.
Although, you know I've used that in the past, not many times.
It's horrible when you get a hundred thousand rows it just takes forever.
Let's try a power pivot solution to this.
So, I have my original data set, create a linked table in the Power pivot tab.
Click [ ok ].
All right! There's my data.
I'll take this back as a pivot table, new worksheet, that's great.
Let's put region, markets and then revenue.
All right! Now, here's where the power of power pivot comes in, take a look at this cell.
How many filters are applied to that cell?
There's actually, two filters.
The first filter says hey, the market has to be Chicago and the second filter says the region has to be central.
We're going to do a calculation here.
Let's a new measure and the calculation is going to be equal calculate.
Calculate is kind of like SUMIF.
I want the sum of table 1, revenue and then here's the filter.
Now, normally in a sum if you apply filters to the data, here I'm actually going to remove a filter.
I want to say, I want to apply all the filters that are naturally upon that cell of the pivot table.
So, this cell has two filters, it has market equal Chicago, region, equal central, but in this case, I'm going to say well, wait for the market.
I don't want any filter, all I want all of the markets.
All of the markets, so all table 1 markets close the...
Calculate, now these formulas are so new and different.
I always check the formula and nowhere is information.
You have to click check from them and hold down check formula.
Otherwise the tool tip covers up the result and what are we going to call this we're going to call this revenue, for region, click [ ok ].
Alright! And check that out.
Now, it's only doing calculations here, 15 calculations.
So, if you had out of thousand rows, it wouldn't have to do 100,000 calculations and add them up.
It's literally only doing this 15 and the beautiful thing here is that, for Chicago is showing us the total revenue for the Central region.
All right! So, a very fast way to do that.
Now, that we have that measured, let's create one new measure, and we call this a percent of region and the answer is going to be...
Alright! So, we have our existing table 1 revenue that has to be, equals sum of table 1 revenue divided by that measure table 1.
I can just put the measure name in there, revenues and region, parenthesis look okay, check formula and that looks good.
Let's click [ ok ], cross our fingers.
There we go, nice.
Now, we need to format that steps back on the pivot table field settings, number formats, percentage, let's go with one, click [ OK ], click [ ok ] and the same answer that Mike came up with but with a large data set.
You know, even with in Power pivot 100 million rows, this is going to be very very quick because they only have to do this calculation 15 times this calculation, 15 times and we're done.
Hey! I want to thank everyone for stopping by, we'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
