Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you create a PowerPivot data model, you can sort, filter, and create a PivotTable to summarize your data. You can also create your own summaries using Data Analysis eXpressions or DAX. DAX expressions are similar to Excel worksheet formulas that you use to summarize values in Excel tables. So just for a quick comparison, if I wanted to find the 2008 sales in the table to the left, I would type =sum( and then we're looking at the values in D3 through D8.
So type a right parentheses and press Enter and there is the formula for just those cells. If I wanted to find a total of all sales in the Sales column of the table then I would type =sum(, and then the name of the table, which is Table1, so T-A-B-L-E1 and then a left square bracket so that I can start naming the column I want to use, and in this case that is Sales, so I'll type in S and then in the formula AutoComplete list I see that sales is highlighted, so I can press Tab to accept it.
Type a right square bracket to close the table column reference and then a right parentheses to close the sum formula. Press Enter and I get my result. And if I highlight that cell again, I can see that I have the table reference followed by the column reference all as an argument inside of the SUM formula. Now let's shift over to PowerPivot and create a calculated column so that you can see how it compares. So I'll click the PowerPivot tab on the Ribbon and then click the PowerPivot Window button, and here I have my PowerPivot data model.
What I want to do is create a new column that finds the total for each line item, so I will be multiplying Quantity by Price. So I will click in the first cell in the Add Column column, and then type equal. , You'll see the typing here in the Formula Bar as opposed to in the cell. So type equal and then a left square bracket and then I can use the Down Arrow key to select the first field I want, and that's Quantity. Press Tab and then in PowerPivot the program encloses the field name in square brackets, so you don't need to type it in yourself.
Now type an asterisk for multiply, then a left square bracket and we're multiplying by Price, so I'll type a P and the highlighted item is Price so I'll press Tab. Everything looks good. And when I press Enter you'll see the PowerPivot creates a calculated column called CalculatedColumn1 and its values are Quantity times the Price. DAX lets you extend the basic capabilities of PowerPivot by creating expressions that summarize your data. In the rest of this chapter I'll go into more detail on how to use DAX to summarize your data.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64651 Viewers
80 Video lessons · 124285 Viewers
52 Video lessons · 60227 Viewers
59 Video lessons · 46062 Viewers