Guest Post By David Hager
It is a fairly easy task in PowerPivot to calculate a sum based on N criteria (after a learning curve). This can be accomplished by filtering your data before importing it in the PowerPivot window, selections made in the Pivot Table, or through some (relatively) simple DAX calculations.
However, if N gets too big, the task get much bigger. Now, imagine a table with thousands of customers and the task is to calculate a sum based on only 1000 of those customers. Conventional filtering does not provide a way to do this. Then, say that this list of customers changes from day to day. It would be nice to have a method to perform this calculation. There is!
The first step is to create a linked table from your Excel workbook to its PowerPivot window. The table in PowerPivot is named LookupList. Then, a relationship is created between that table and an existing Table1 (as shown below).
The list shown above does not have 1000 entries, and is just for demonstration purposes. However, I have tested it with >1500 entries and it works 🙂
Solution One: Calc Column as Basis for Measure
One solution is to create a calculated column named AggregateList that returns only the amounts that are associated with the customers in the lookup table. The correct formula to do this is:
Now, a pivot table can generate the desired result as shown below.
By changing the aggregation of the measure, an average or other value can also be obtained.
Solution Two: Yes/No Column plus CALCULATE Measure
(From Rob): Rather than produce the numerical column as David did above, my first instinct was to use the LookupTable to generate a simple yes/no column, and then use that as the filter in a CALCULATE measure.
and then the measure:
This is mostly a matter of style. Both require a calc column – this option’s calc column is simpler but has a more complex measure. (Neither approach is all that complicated however.) This option might make for a smaller file size, but it’s hard to be certain of that, and even if true, it won’t be much.
Back to David.
Refreshing the customer list
I mentioned at the beginning of this article that the customer list provided by the linked table is dynamic by nature. If the list is changed manually, recalculation will occur if the PowerPivot window is opened. However, if the list is generated from Excel formulas some calculation errors can occur. Instead, the use of other methods to update this list is preferable. The details of these issues will be discussed in a future article.