See how to calculate columns in the PowerPivot grid using DAX.
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 6 - Calculated columns.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Well, this chapter we're taking a look at how to use the DAX language, right in the PowerPivot grid, to add new calculated columns.
Before we do that though, just in case you're not familiar with these functions in Excel, let's talk about some great date handling functions in Excel.
So, let's say that we have a date up here, July 6, 7/6/2010, and I want to break that down into a year, month, and day.
Excel gives us three functions that make this really, really easy.
So =YEAR of that date will give us 2010, =MONTH on that date will give us 7 for July, and =DAY of that date will give us just the 6th.
OK, so those are building, unfortunately, they give us numbers, of course, numbers are easy to sort, but you know, it may not be how we want things to appear.
So lots of times I will use the TEXT function, so =TEXT of that date, press F4 there to lock that down, and then say “Format it as shown here to the left”.
So if we use the MMM format, you see we get Jul for July, I'll double click to copy this down.
4 M’s will actually spell out the month of July, a single D will give us the day in a single digit for dates 1st-9th, or two digits for 10th-31st.
But if you specify DD, then you get the very nice, sort friendly, two digits all the time, so instead of 6, you get 06.
3 D’s gives you the day of the week abbreviation, 4 Ds actually spells out the day of the week, and then you can start to get creative.
For example, YY-MM would give you the year, a dash, and then the two-digit abbreviation.
Or let's try YY-MMM to actually give us the year and the month.
Now that one's going to be really, really hard to sort properly in PowerPivot, so that one's probably not even practical.
OK, so let's go and see how we can use that knowledge from Excel in the PowerPivot window.
So we have a date field here, and we want to add a new calculated column, so we just start with an equal sign, and type the function that you want to use, so I'm going to use the YEAR function.
You notice as soon as I type Y, they offer a couple of choices here, I'll press Tab to choose YEAR, and then they want to know which field.
OK now, hardcore Excel people know that we can use the arrow keys to go back to that field.
Unfortunately, PowerPivot does not support the arrow keys, you have to either start to type the field name, or just use the mouse to click on it.
I know most hardcore people hate to use the mouse, but here we are, that's just what we have to do.
Click the closing parenthesis, and very quickly it will give us the year column for those 1.8 million rows.
Again, bad heading, I wish they would just prompt us for the heading, because we're going to have to change that heading every single time, I'll call this year.
Alright, and I could do similar things for month and for day using the MONTH and DAY functions.
Again, most functions that we use here in this grid are exactly like the functions that we use in Excel.
The one glaring difference is, the TEXT function which is so commonly used in Excel, is not spelled TEXT here, it's spelled as FORMAT.
So we'll use =FORMAT, click on the field that we want, comma, and then in quotes, whatever format we would like to use.
So I'm going to spell it the month completely, that would be 4 M's in quotes, closing quote, closing parenthesis, press Enter, and let that calculate down.
And again, once we get the answer, you have to right-click and change that horrible heading to something a bit more friendly.
Alright, so there you have it, just a sampling of some of the many DAX functions that you can use to create new calculations right here in the grid.
Chapter 6 has a complete list of all those DAX functions and examples of how to use them, this is the one that you're going to run into most often, so, this is what we do in the podcast.
Alright well, 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 6 - Calculated columns.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Well, this chapter we're taking a look at how to use the DAX language, right in the PowerPivot grid, to add new calculated columns.
Before we do that though, just in case you're not familiar with these functions in Excel, let's talk about some great date handling functions in Excel.
So, let's say that we have a date up here, July 6, 7/6/2010, and I want to break that down into a year, month, and day.
Excel gives us three functions that make this really, really easy.
So =YEAR of that date will give us 2010, =MONTH on that date will give us 7 for July, and =DAY of that date will give us just the 6th.
OK, so those are building, unfortunately, they give us numbers, of course, numbers are easy to sort, but you know, it may not be how we want things to appear.
So lots of times I will use the TEXT function, so =TEXT of that date, press F4 there to lock that down, and then say “Format it as shown here to the left”.
So if we use the MMM format, you see we get Jul for July, I'll double click to copy this down.
4 M’s will actually spell out the month of July, a single D will give us the day in a single digit for dates 1st-9th, or two digits for 10th-31st.
But if you specify DD, then you get the very nice, sort friendly, two digits all the time, so instead of 6, you get 06.
3 D’s gives you the day of the week abbreviation, 4 Ds actually spells out the day of the week, and then you can start to get creative.
For example, YY-MM would give you the year, a dash, and then the two-digit abbreviation.
Or let's try YY-MMM to actually give us the year and the month.
Now that one's going to be really, really hard to sort properly in PowerPivot, so that one's probably not even practical.
OK, so let's go and see how we can use that knowledge from Excel in the PowerPivot window.
So we have a date field here, and we want to add a new calculated column, so we just start with an equal sign, and type the function that you want to use, so I'm going to use the YEAR function.
You notice as soon as I type Y, they offer a couple of choices here, I'll press Tab to choose YEAR, and then they want to know which field.
OK now, hardcore Excel people know that we can use the arrow keys to go back to that field.
Unfortunately, PowerPivot does not support the arrow keys, you have to either start to type the field name, or just use the mouse to click on it.
I know most hardcore people hate to use the mouse, but here we are, that's just what we have to do.
Click the closing parenthesis, and very quickly it will give us the year column for those 1.8 million rows.
Again, bad heading, I wish they would just prompt us for the heading, because we're going to have to change that heading every single time, I'll call this year.
Alright, and I could do similar things for month and for day using the MONTH and DAY functions.
Again, most functions that we use here in this grid are exactly like the functions that we use in Excel.
The one glaring difference is, the TEXT function which is so commonly used in Excel, is not spelled TEXT here, it's spelled as FORMAT.
So we'll use =FORMAT, click on the field that we want, comma, and then in quotes, whatever format we would like to use.
So I'm going to spell it the month completely, that would be 4 M's in quotes, closing quote, closing parenthesis, press Enter, and let that calculate down.
And again, once we get the answer, you have to right-click and change that horrible heading to something a bit more friendly.
Alright, so there you have it, just a sampling of some of the many DAX functions that you can use to create new calculations right here in the grid.
Chapter 6 has a complete list of all those DAX functions and examples of how to use them, this is the one that you're going to run into most often, so, this is what we do in the podcast.
Alright well, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
