Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you create a measure using an Aggregate function, PowerPivot calculates that measure's value for every row summarized in the PivotTable field. You can filter your PivotTable to limit your data but if you find yourself creating the same filter many times, you should consider creating a measure that incorporates that filter into its formula. In this movie, I'll show you how to do exactly that. So, we will jump into PowerPivot, clicking the PowerPivot tab on the Ribbon and the PowerPivot Window button, and we will create a measure that includes the filtered formula.
So to do that, we will click the PivotTable button to create a PivotTable andwe will put it on the new worksheet in Excel and click OK. Now remember that we need to use an Aggregate function such as SUMX for the filter to work. So, I'll create a new measure and I'll name it LargerOrders, and I will go down to Formula box. In an Aggregate function, you have the name of the function followed by the table and then the expression.
And the table argument can be a real table or it can be a virtual table that's created using another function, and in PowerPivot you can use the Filter function to create a virtual table that's a subset of the original table. To give you an example, I'll create the formula =sumx(Filter( and now the Filter function takes two arguments: the table that you're referring to and then the actual filter.
In this case, the table's name is Data. Type a comma and now I create a rule that does the filtering. And in this case, I only want to see orders with quantities greater than 18, so in other words 19 or more items. So, for that I will type in Data, a square bracket, and then we are referring to the quantity field so I will type a Q then a Tab to accept that field then a greater than sign and then the number 18. Now I type a right parentheses to close the Filter function and now I'm back in the SUMX function.
Now I will type a comma and now I can type the expression. So, first we'll have the data quantity field. So Data, left square bracket, q for quantity, press Tab, and I'm going to multiply that by the price field. So type an asterisk then data, left square bracket, and p for price. It's the highlighted items so I can press Tab to accept it then a right parentheses. And the formula looks good, so I can click OK.
So, you can see from the body of the PivotTable that the total of my larger orders appears in cell B4. Now, let's compare that to the total of all orders. So, to do that, I will drag the OrderTotal field to the Values area and you can see that the OrderTotal is greater than LargerOrders. The opposite function of the Filter function is called ALL and that ignores any filters in the data model or in the PivotTable. To illustrate that, I'll create another new measure and I will name this one AllStoreSales. Then in the Formula box I will create the formula SUMX. Again we need to use an Aggregate function, left parentheses, the keyword All, then a left parentheses, and then I want every row in the data table so I will type Data, right parentheses, a comma, and now I can type my expression, which will be once again that the Price field times the Quantity field.
So, Data, left square bracket, quantity, type q, and then Tab to accept the highlighted field, asterisk, and then data, left square bracket, p for price. It's highlighted, press Tab to accept it, then a right parentheses, and click OK. When I do, Excel adds that field to the data area of the PivotTable. Now, we will see what happens when I create a report filter based on the StoreID field. So, I'll drag the StoreID field down to the Report Filter area and then I will go up and click the Filter arrow.
Click the expand control next to All and then I'll check the Select Multiple Items box, clear the check box next to All, and then we will just look at the values for Store 1. So, I will click OK and Excel filters the PivotTable. So, you'll notice that the LargerOrders field changed its value, the Sum of OrderTotal field changed its value, but the AllStoreSales field did not change its value and that's because the measure used to produce its value ignores any filters applied to the table.
Building a filter into an aggregate function saves you time when you summarize data in PowerPivot. Be sure to name any filtered measures clearly to assure your PivotTable summarizes exactly the data you want it to. Alternatively, if you always want to measure to display the total of every row in the data model, use the All function in your measure's formula.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97700 Viewers
80 Video lessons · 141117 Viewers
59 Video lessons · 59472 Viewers
52 Video lessons · 72776 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.