Perhaps you've used Calculated Fields in traditional pivot tables. DAX Measures make that feature look like it was designed by someone in kindergarten. Learn how to use the Calculate function to create DAX Measures.
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
Transcript of the video:
MrExcel podcast is sponsored by “Easy-XL”.
PowerPivot for the Data Analyst chapter 10 – DAX Measures. Chapter 10 is probably the hardest chapter in the whole book, but that's because DAX measures are the most powerful feature, I think, in PowerPivot.
OK, so before, we added a calculated column back in the PowerPivot grid, and that was calculated 1.8 million times, because I had 1.8 million records, these DAX measures are only going to be calculated for each cell in the Pivot table.
So here I have a little Pivot table, basically it’s calculating five values, so if I had a DAX measure, it's only going to do five calculations.
Alright, so that's interesting, and we have to talk about this, let's talk about this one cell right here, how many different filters are applied to this one cell.
OK well, obviously, region Arizona, that's one filter, division jewelry is another filter, but there's two more filters.
The row fields and column fields really are filters, so we're filtering down to the month of April, and where the mall developer is General Growth Properties.
So there's four filters apply to this, and we're going to use a DAX function called CALCULATE.
And the interesting thing about CALCULATE, it's sort of like SUMIFS in Excel.
But what's interesting is, if you don't specify any conditions at all, you automatically get all of the filters that apply to this cell.
So if I don't specify any conditions, I'm automatically going to get four filters, the month, the mall developer, the region and division.
And as I change things, those DAX measures will automatically calculate.
Alright, so we want to create a new measure.
I always go back to the PowerPivot tab here and choose New Measure, and what I'm going to do is, I'm going to create a measure that will show me total handbag sales.
So, no matter what I have selected here in division, I'm going to override that.
So the measure name is going to be Handbag, let's go with Handbags, and then I start to build a formula.
So the formula’s going to be =CALCULATE, and I want the SUM, you always have to choose an aggregate function here, the SUM of sales.
Now I have to remember that the name of this table was called demo, and that's what it was called when we imported a long time ago, so I just type “de” and I'll have demo[ Revenue ].
So demo[ Revenue ]), and then a comma, and now it wants the filter.
So if I did nothing, I would get the total revenue for all of the filters.
But here I want to override one of the existing filters, so I want that division field to be =Handbags, instead of whatever happens to be selected in the slicer.
So I'm going to say demo[ Division ]=”Handbags”, and now I'm done with that criteria.
I could add additional criteria, for example, if I needed an override region or some other field that was filtered.
Alright now, you always want to check your formula, there's an interesting bug here.
You click Check Formula, and unfortunately, the tooltip, every single time, covers up the answer.
So it tells us whether the formula was good there or not, but for some reason that the tooltip always covers it up.
So click Check Formula again to get rid of that tooltip.
Just an annoying thing, I thought this would have been fixed after the beta, but it still exists there in the final version. So, just go figure, click OK.
So there's our new calculation, now let's do a little test, let's filter the report to just handbags, and you'll see that we're getting the exact right answer.
It's the exact same thing that we would have gotten before, but that Handbags column will give us the handbags, no matter what is selected here.
So now we can actually start to do a calculation of jewelry versus handbags.
Alright, so that's the amazing thing about DAX measures, is the DAX measure can choose to ignore certain filters that are applied to the Pivot table, it allows a whole bunch of very interesting calculations.
The book, chapter 10 goes through a lot of those calculations.
In the next video, chapter 11, we'll take a look at how we can use the time intelligence functions to compare sales from a different time period using a DAX measure.
So three different chapters in the book cover DAX measures, the first one calculated fields, and then this one covering basically the CALCULATE function, and then chapter 11 talking about the time intelligence.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
PowerPivot for the Data Analyst chapter 10 – DAX Measures. Chapter 10 is probably the hardest chapter in the whole book, but that's because DAX measures are the most powerful feature, I think, in PowerPivot.
OK, so before, we added a calculated column back in the PowerPivot grid, and that was calculated 1.8 million times, because I had 1.8 million records, these DAX measures are only going to be calculated for each cell in the Pivot table.
So here I have a little Pivot table, basically it’s calculating five values, so if I had a DAX measure, it's only going to do five calculations.
Alright, so that's interesting, and we have to talk about this, let's talk about this one cell right here, how many different filters are applied to this one cell.
OK well, obviously, region Arizona, that's one filter, division jewelry is another filter, but there's two more filters.
The row fields and column fields really are filters, so we're filtering down to the month of April, and where the mall developer is General Growth Properties.
So there's four filters apply to this, and we're going to use a DAX function called CALCULATE.
And the interesting thing about CALCULATE, it's sort of like SUMIFS in Excel.
But what's interesting is, if you don't specify any conditions at all, you automatically get all of the filters that apply to this cell.
So if I don't specify any conditions, I'm automatically going to get four filters, the month, the mall developer, the region and division.
And as I change things, those DAX measures will automatically calculate.
Alright, so we want to create a new measure.
I always go back to the PowerPivot tab here and choose New Measure, and what I'm going to do is, I'm going to create a measure that will show me total handbag sales.
So, no matter what I have selected here in division, I'm going to override that.
So the measure name is going to be Handbag, let's go with Handbags, and then I start to build a formula.
So the formula’s going to be =CALCULATE, and I want the SUM, you always have to choose an aggregate function here, the SUM of sales.
Now I have to remember that the name of this table was called demo, and that's what it was called when we imported a long time ago, so I just type “de” and I'll have demo[ Revenue ].
So demo[ Revenue ]), and then a comma, and now it wants the filter.
So if I did nothing, I would get the total revenue for all of the filters.
But here I want to override one of the existing filters, so I want that division field to be =Handbags, instead of whatever happens to be selected in the slicer.
So I'm going to say demo[ Division ]=”Handbags”, and now I'm done with that criteria.
I could add additional criteria, for example, if I needed an override region or some other field that was filtered.
Alright now, you always want to check your formula, there's an interesting bug here.
You click Check Formula, and unfortunately, the tooltip, every single time, covers up the answer.
So it tells us whether the formula was good there or not, but for some reason that the tooltip always covers it up.
So click Check Formula again to get rid of that tooltip.
Just an annoying thing, I thought this would have been fixed after the beta, but it still exists there in the final version. So, just go figure, click OK.
So there's our new calculation, now let's do a little test, let's filter the report to just handbags, and you'll see that we're getting the exact right answer.
It's the exact same thing that we would have gotten before, but that Handbags column will give us the handbags, no matter what is selected here.
So now we can actually start to do a calculation of jewelry versus handbags.
Alright, so that's the amazing thing about DAX measures, is the DAX measure can choose to ignore certain filters that are applied to the Pivot table, it allows a whole bunch of very interesting calculations.
The book, chapter 10 goes through a lot of those calculations.
In the next video, chapter 11, we'll take a look at how we can use the time intelligence functions to compare sales from a different time period using a DAX measure.
So three different chapters in the book cover DAX measures, the first one calculated fields, and then this one covering basically the CALCULATE function, and then chapter 11 talking about the time intelligence.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
