Today, in Episode #1327, Bill looks at DAX Measures in Power Pivot 2010. Specify your conditions, learn to manipulate the 'Calculate' Function to customize your measurements in Power Pivot 2010. "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, okay.
So, before we added a calculated column back in the power pivot grid and that was calculated 1.8 million times because I have 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 that basically is calculating five values.
So, if I add a DAX measure, it's only going to do five calculations.
All right, 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?
Okay, 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 our 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 the cell.
So, if I don't specify any any conditions, I'm automatically going to get four filters.
The Month, the Mall Developer, the Region and Division is like change things.
Those DAX measures will automatically calculate it.
All right, 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 overwrite 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 formulas going to be equal calculate All right , we use 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 D, E and I'll have demo revenue, so sum, demo revenue, comma, closing parenthesis 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 overwrite one of the existing filters.
So, I want that division field to be equal to handbags, instead of whatever happens to be selected in the slicer.
So, I'm going to say demo division equal to in quotes hand bags close the quotes and now I'm done with that criteria.
I could add additional criteria for example If I needed to overwrite region or some other field, that was filtered.
All right, now you always want to check your formula.
There's an interesting bug here, you click check formula and unfortunately the tool tip 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 tool tip always covers it up.
So, click check formula again to get rid of that tool tip, just an annoying thing.
I thought this would have been fixed after the the Beta, but it still exists there in the final version.
So, just go figure, click [ ok ].
Alright, so there's our new calculation.
Now here, 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 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.
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.
I want to thank you for stopping by, 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, okay.
So, before we added a calculated column back in the power pivot grid and that was calculated 1.8 million times because I have 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 that basically is calculating five values.
So, if I add a DAX measure, it's only going to do five calculations.
All right, 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?
Okay, 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 our 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 the cell.
So, if I don't specify any any conditions, I'm automatically going to get four filters.
The Month, the Mall Developer, the Region and Division is like change things.
Those DAX measures will automatically calculate it.
All right, 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 overwrite 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 formulas going to be equal calculate All right , we use 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 D, E and I'll have demo revenue, so sum, demo revenue, comma, closing parenthesis 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 overwrite one of the existing filters.
So, I want that division field to be equal to handbags, instead of whatever happens to be selected in the slicer.
So, I'm going to say demo division equal to in quotes hand bags close the quotes and now I'm done with that criteria.
I could add additional criteria for example If I needed to overwrite region or some other field, that was filtered.
All right, now you always want to check your formula.
There's an interesting bug here, you click check formula and unfortunately the tool tip 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 tool tip always covers it up.
So, click check formula again to get rid of that tool tip, just an annoying thing.
I thought this would have been fixed after the the Beta, but it still exists there in the final version.
So, just go figure, click [ ok ].
Alright, so there's our new calculation.
Now here, 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 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.
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.
I want to thank you for stopping by, see you next time for another netcast from MrExcel.
