Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you create measures in PowerPivot you can run into unexpected problems when you use functions such as SUM or COUNT. In this movie I'll show you how to avoid those issues by using aggregate functions such as SUMX and COUNTX. So to begin let's go into PowerPivot. Click the PowerPivot tab on the Ribbon and then the PowerPivot Window button and our data reappears inside of PowerPivot. Now in the previous movie I showed you how to create a calculated column and a measure.
The difference between the two is that a calculated column uses values within a table row to generate its result while a measure summarizes the values for an entire column. This data model has two columns, Price and Quantity, that I can use to calculate a total. I can if I want to create a calculated column such as by adding a formula equal and I'm just repeating what I did from the previous movie. [Price]*[Quantity]. Press Tab to accept it and press Enter.
So there is my calculated column and I won't bother renaming it this time. Now if I want to create a measure from those two columns, I need to create a PivotTable and then create the measure. So to do that I click the PivotTable button and put it on the worksheet, click okay and I get my new PivotTable. Now let's say that I want to create a new measure called OrderTotal. To do that I click the New Measure button and then I'll change the name of the measure to OrderTotal. And you don't have to start with a capital letter. I just do it out of convention. And then we can create the formula.
So I'll click in the Formal box and this formula will generate an error. So it's =sum(Data, left square bracket, Price. So I'll start with the P and select that total. Type an asterisk and then type in data and a left square bracket and then Q for quantity, press Tab to accept the field, right parenthesis to close formula, and click OK. Now you can see what I did that I got an error; in other words it says the calculation has been aborted and the error message says that the SUM function only accepts a column reference as an argument.
So that means that I need to go in and to change the formula. So I will right-click the OrderTotal field name here in the field list and click Edit Formula. So the formula that I'm going to create is called an aggregate formula and the syntax is the function name followed by an X then the table name and then the expression. So in this case the formula that I want create is =sumX( and then Data, which is the name of the table, then a comma, then I can create the expression, and the expression will be what I created before. So the Data table, left parenthesis, and then price, typing a P and then pressing Tab to accept it, then an asterisk and then now we want the Quantity column from the data table, so that would be Data( and then a Q for quantity and then press Tab to accept it and then a right parenthesis to close the formula.
Now when I click OK, Excel finds the value and the formula operates normally. So once again let's go back into the measure. So with the measure selected, you can click Measure Settings to display the Measure Settings dialog box and the formula reads sumx and then the name of the table and then the expression that uses values from within that. I don't want to make any changes so I'll click Cancel. In addition to the SUMX formula, you can create COUNTX, COUNTAX, AVERAGEX, MAXX and MINX functions, which correspond to the COUNT, COUNTA, AVERAGE, MAX and MIN worksheet functions.
Aggregate functions let you summarize data by creating measures using values from multiple columns within a data model. If you can't get the calculation you want by using a regular function such as sum, consider using its aggregate counterpart.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69972 Viewers
80 Video lessons · 127391 Viewers
52 Video lessons · 62346 Viewers
59 Video lessons · 48043 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.