Kelly wants to display a Range in a Pivot Table. Unfortunately, Calculated Fields in a Pivot Table look at each individual row, so the MAX(B2)-MIN(B2) will always be zero. PowerPivot and the DAX Measure come to the rescue in Episode #1390.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, episode 1390.
Max-Min in a Pivot?
Hey! Welcome back to the MrExcel netcast.
This question came in from Kelly.
It was actually my article in CFL.com, a few weeks ago, and Kelly was trying to create a pivot table.
That would show the Min and Max, for each product.
Which is simple enough in a pivot table, but then she wanted to show the range between the min and the max.
Let's do that real quick, I'll choose product.
I'll take price and drag it to values, twice.
This first one, we're gonna change to a min.
So, we go in the field settings, choose min and I'll call it, min price.
Click [ OK ].
This one, field settings, will choose max.
I call it max price.
Okay! Then in the pivot table, Kelly wants to figure out what the range is the max minus the min and if we go out to calculations fields items and sets.
Calculated field and create something called range, which is equal to, equal max of price, minus min of price.
Click [ add ], click [ OK ].
You see, the range is zero, everywhere.
All right! And unfortunately, what's happening here is Excel is treating this on a row by row basis.
So, they're adding in essence, in adding a calculation of memory called range.
Equal max of this number minus the min of that number of course, that's gonna be zero all the way down and there's gonna be nothing other than zeros there.
So, that's just not working.
You know, it's been one of these classic things that pivot tables can not do.
Now, you know there's a new product for Excel 2010 called power pivot.
Power pivot has six amazing things that it can do and one of those is use the DAX language, to write calculated fields that are better than anything.
We've ever had in reguler pivot tables.
Okay! Now, power pivot is free.
If you have Excel 2010, just got to powerpivot.com.
I'm gonna take this data, we're gonna run through.
First thing, I'm going to do is I'm going create a table out of this [ ctrl + T ].
Click [ OK ] and then on the power pivot tab, I'm gonna say create a linked table.
So, now we're actually in the power of the pivot window with that data.
That's all I have to do in PowerPivot.
I now click pivot table, and we're back in Excel with a power pivot field list.
So, we'll choose product and now I'm gonna do three calculated fields. I'm going to do, the min price, the max price and then the range.
In power pivot, a calculated field is in color calculated field is called measures or like new measure and let's see our table is table one.
There's only one table here, power pivot allows you to do multiple tables but in this case, it's easy.
We'll call it min P and that is gonna be equal to min.
Not just min, min X.
Min X says, the minimum of an expression.
All right! And now, I need to use the right nomenclature here but luckily I don't have to remember.
I just got a type T and we're going to get table price, let's click there.
All right! So, the min X of table 1, price.
How, we know, they're wrong.
First we have to say, which table we're going against?
We're against table 1 comma and then the expression, click [ OK ].
Now, admittedly that was a lot harder than just choosing price and then minimum and renaming it.
Let's do that, again.
This time we're gonna call it max P.
So, equal max again, the X to say, it's an expression.
Table 1, table 1 price.
Click [ OK ].
All right! So now, we have two measures, we're add one more measure.
This is the cool thing.
We're gonna call this range, a measure in power pivot, is allowed to reach forward to other measures refer to other measures.
So, we're gonna say equal max P minus actually, specify the table.
Table max P minus table min P.
There's our formula, to check the formula see that there's no errors in the formula, great.
Click [ OK ] and now, actually in the pivot table we have a range.
So, 28 minus 18 is a range of tab.
All right! Power pivot does amazing things.
100 million records joined in two tables, together without vlookup, but in this particular case, it's solved this problem that Kelly had.
So very, very cool, cool trick.
All right! Well, want to thank Kelly for sending that question in and want to thank you for stopping by, we'll see you next time for another netcast.
Hey! By the way power pivot for the Excel, data a list if you are interested in Power Pivot, right there.
That's my best-selling book on power pivot, check that one out.
It doesn't cover the server version of Power Pivot is just...
Thanks for stopping by, see you next time for another netcast MrExcel.
Learn Excel from MrExcel, episode 1390.
Max-Min in a Pivot?
Hey! Welcome back to the MrExcel netcast.
This question came in from Kelly.
It was actually my article in CFL.com, a few weeks ago, and Kelly was trying to create a pivot table.
That would show the Min and Max, for each product.
Which is simple enough in a pivot table, but then she wanted to show the range between the min and the max.
Let's do that real quick, I'll choose product.
I'll take price and drag it to values, twice.
This first one, we're gonna change to a min.
So, we go in the field settings, choose min and I'll call it, min price.
Click [ OK ].
This one, field settings, will choose max.
I call it max price.
Okay! Then in the pivot table, Kelly wants to figure out what the range is the max minus the min and if we go out to calculations fields items and sets.
Calculated field and create something called range, which is equal to, equal max of price, minus min of price.
Click [ add ], click [ OK ].
You see, the range is zero, everywhere.
All right! And unfortunately, what's happening here is Excel is treating this on a row by row basis.
So, they're adding in essence, in adding a calculation of memory called range.
Equal max of this number minus the min of that number of course, that's gonna be zero all the way down and there's gonna be nothing other than zeros there.
So, that's just not working.
You know, it's been one of these classic things that pivot tables can not do.
Now, you know there's a new product for Excel 2010 called power pivot.
Power pivot has six amazing things that it can do and one of those is use the DAX language, to write calculated fields that are better than anything.
We've ever had in reguler pivot tables.
Okay! Now, power pivot is free.
If you have Excel 2010, just got to powerpivot.com.
I'm gonna take this data, we're gonna run through.
First thing, I'm going to do is I'm going create a table out of this [ ctrl + T ].
Click [ OK ] and then on the power pivot tab, I'm gonna say create a linked table.
So, now we're actually in the power of the pivot window with that data.
That's all I have to do in PowerPivot.
I now click pivot table, and we're back in Excel with a power pivot field list.
So, we'll choose product and now I'm gonna do three calculated fields. I'm going to do, the min price, the max price and then the range.
In power pivot, a calculated field is in color calculated field is called measures or like new measure and let's see our table is table one.
There's only one table here, power pivot allows you to do multiple tables but in this case, it's easy.
We'll call it min P and that is gonna be equal to min.
Not just min, min X.
Min X says, the minimum of an expression.
All right! And now, I need to use the right nomenclature here but luckily I don't have to remember.
I just got a type T and we're going to get table price, let's click there.
All right! So, the min X of table 1, price.
How, we know, they're wrong.
First we have to say, which table we're going against?
We're against table 1 comma and then the expression, click [ OK ].
Now, admittedly that was a lot harder than just choosing price and then minimum and renaming it.
Let's do that, again.
This time we're gonna call it max P.
So, equal max again, the X to say, it's an expression.
Table 1, table 1 price.
Click [ OK ].
All right! So now, we have two measures, we're add one more measure.
This is the cool thing.
We're gonna call this range, a measure in power pivot, is allowed to reach forward to other measures refer to other measures.
So, we're gonna say equal max P minus actually, specify the table.
Table max P minus table min P.
There's our formula, to check the formula see that there's no errors in the formula, great.
Click [ OK ] and now, actually in the pivot table we have a range.
So, 28 minus 18 is a range of tab.
All right! Power pivot does amazing things.
100 million records joined in two tables, together without vlookup, but in this particular case, it's solved this problem that Kelly had.
So very, very cool, cool trick.
All right! Well, want to thank Kelly for sending that question in and want to thank you for stopping by, we'll see you next time for another netcast.
Hey! By the way power pivot for the Excel, data a list if you are interested in Power Pivot, right there.
That's my best-selling book on power pivot, check that one out.
It doesn't cover the server version of Power Pivot is just...
Thanks for stopping by, see you next time for another netcast MrExcel.
