Kelly wants to display a Delta 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.
In this video, a DAX Measure solves the problem. But to unlock DAX, you need to choose Add This Data To The Data Model.
Table of Contents
(0:00) Need Max minus Min in pivot table
(0:31) MIN and MAX are easy
(0:51) Calculated Field fails
(1:51) Add to Data Model
(2:51) New Measure
(4:25) Wrap-up
In this video, a DAX Measure solves the problem. But to unlock DAX, you need to choose Add This Data To The Data Model.
Table of Contents
(0:00) Need Max minus Min in pivot table
(0:31) MIN and MAX are easy
(0:51) Calculated Field fails
(1:51) Add to Data Model
(2:51) New Measure
(4:25) Wrap-up
Transcript of the video:
Learn Excel from MrExcel Podcast, episode 2529. Calculate max minus men in a pivot table.
This is revisiting an old video from April 2011.
It's still getting some views and it's really out of date.
So our goal here is we have several products, nine in this case, sold at various prices.
And I want to know the min price, the max price and the difference. That's the hard part.
And if you just try and do this with a regular pivot table, we put products down the side, price two times in the values area, very easy to get min and max, double click here and choose min.
Double click here and choose max. No problems with that.
But now we want to get the difference. So 28 minus 18, there's a $10 delta there.
And if you just do this in a regular pivot table, go to fields, items, and sets, calculated field, we'll call it delta, and that's going to be equal to max of price minus the min of price.
Click okay. You see we get zeros all the way down.
And what is essentially happening here is it is essentially doing this calculation behind the scenes.
Max equal max of the price on this row, min is the equal min of the price on this row.
And then the delta is this minus this, which of course is going to be zero all the way down.
All right.
So here is the awesome, awesome solution to be able to calculate the min minus max in a pivot table.
It is not obvious at all.
I'm going to start with this data set, insert pivot table.
The one difference here is I'm going to check this box for add this data to the data model, which is the worst sentence in Excel.
In this case, the check boxers say unlock the amazing benefits of the DAX formula language.
So I choose that, click okay.
You'll notice it takes a tiny bit longer to create the pivot table, especially if it's the first data model pivot table that you create today.
We'll take product and put it in rows just like before.
Oh, my friend Rob Coley is going to just howl that I create an implicit measure here, but I'm going to do that. I'm going to take the price and drop it in values.
I'm going to take the price and drop it in values again.
I'm going to double click here and choose min.
I'm going to double click here and choose max.
This all feels just like the first pivot table, but there's a huge difference here in that we are allowed to create amazing calculations on the power pivot tab using measures new measure.
Now, some people don't have the power pivot tab and starting in Excel 2016, you can come over here to the table name, which in my case is range, right click and say add measure, right?
So I actually like to show this one because it's safer in that some people don't have the power pivot tab.
All right.
Our measure name is going to be called delta, and here, equals sign, I'm going to hit a left square bracket and find max of price and then minus left square bracket and choose min of price.
What's the big difference?
DAX is actually going into the pivot table and not using the underlying data, but using this number 18, and this number 28.
28 minus 18 is going to be 10. Can't hurt to check the DAX formula.
If you wanted to, you could apply a currency format right here.
I didn't even take the time to apply the currency formats there because it requires extra clicks. But I guess we're here and so we're fine.
Now, because you use this method of right clicking on the range, the new field gets added to the field list, but you have to check it to include it.
Had you used the power pivot measures add measure, it would've gotten added automatically.
All right, so there we are, our pivot table for each product.
We can see the minimum price, the maximum price, and then what the difference is.
So 158 to 199 is a $41 delta from the lowest price to the highest price.
Again, the key to this whole thing is as you create the pivot table, insert pivot table, checking that box, add this data to the data model, so many times choosing that box unlocks amazing features in Excel. Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
This is revisiting an old video from April 2011.
It's still getting some views and it's really out of date.
So our goal here is we have several products, nine in this case, sold at various prices.
And I want to know the min price, the max price and the difference. That's the hard part.
And if you just try and do this with a regular pivot table, we put products down the side, price two times in the values area, very easy to get min and max, double click here and choose min.
Double click here and choose max. No problems with that.
But now we want to get the difference. So 28 minus 18, there's a $10 delta there.
And if you just do this in a regular pivot table, go to fields, items, and sets, calculated field, we'll call it delta, and that's going to be equal to max of price minus the min of price.
Click okay. You see we get zeros all the way down.
And what is essentially happening here is it is essentially doing this calculation behind the scenes.
Max equal max of the price on this row, min is the equal min of the price on this row.
And then the delta is this minus this, which of course is going to be zero all the way down.
All right.
So here is the awesome, awesome solution to be able to calculate the min minus max in a pivot table.
It is not obvious at all.
I'm going to start with this data set, insert pivot table.
The one difference here is I'm going to check this box for add this data to the data model, which is the worst sentence in Excel.
In this case, the check boxers say unlock the amazing benefits of the DAX formula language.
So I choose that, click okay.
You'll notice it takes a tiny bit longer to create the pivot table, especially if it's the first data model pivot table that you create today.
We'll take product and put it in rows just like before.
Oh, my friend Rob Coley is going to just howl that I create an implicit measure here, but I'm going to do that. I'm going to take the price and drop it in values.
I'm going to take the price and drop it in values again.
I'm going to double click here and choose min.
I'm going to double click here and choose max.
This all feels just like the first pivot table, but there's a huge difference here in that we are allowed to create amazing calculations on the power pivot tab using measures new measure.
Now, some people don't have the power pivot tab and starting in Excel 2016, you can come over here to the table name, which in my case is range, right click and say add measure, right?
So I actually like to show this one because it's safer in that some people don't have the power pivot tab.
All right.
Our measure name is going to be called delta, and here, equals sign, I'm going to hit a left square bracket and find max of price and then minus left square bracket and choose min of price.
What's the big difference?
DAX is actually going into the pivot table and not using the underlying data, but using this number 18, and this number 28.
28 minus 18 is going to be 10. Can't hurt to check the DAX formula.
If you wanted to, you could apply a currency format right here.
I didn't even take the time to apply the currency formats there because it requires extra clicks. But I guess we're here and so we're fine.
Now, because you use this method of right clicking on the range, the new field gets added to the field list, but you have to check it to include it.
Had you used the power pivot measures add measure, it would've gotten added automatically.
All right, so there we are, our pivot table for each product.
We can see the minimum price, the maximum price, and then what the difference is.
So 158 to 199 is a $41 delta from the lowest price to the highest price.
Again, the key to this whole thing is as you create the pivot table, insert pivot table, checking that box, add this data to the data model, so many times choosing that box unlocks amazing features in Excel. Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
