skip to Main Content

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).

image

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:

=IF(ISBLANK(RELATED(LookupList[Customer])),BLANK(),Table1[AMOUNT])

Now, a pivot table can generate the desired result as shown below.

image

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.

Calc column:

=IF(RELATED(LookupList[Customer])<>"",1,0)

image

and then the measure:

=CALCULATE(SUM(Table1[AMOUNT]),Table1[IncludeCustomer]=1)

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.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 6 Comments
  1. Hi,
    I’m hoping you can help!! I am trying to calculate what the commission should be in column “Com” based upon the criteria laid out in the second section. I have used the following IF statement but it returns 25% for any value entered in the “Total” column:

    =IF(A2>240000*B2>60,A2*0.25,(IF(A2<240000*B2<60,A2*0.15),IF(A2<120000*B2<30,A2*0.1,(IF(A2<60000,(A2-5000)*0.1),IF(A2,5000,0)))))

    Total Points NB? Com
    4000 31 2 1000
    3000 5 3 750
    20000 1 0 5000
    40000 4 5 10000
    70000 30 5 17500
    12000 25 4 3000

    Criteria

    Total Points Com
    <5000 0
    <60000 10%
    <120000 <30 10%
    <240000 240000 >60 25%

    Can you help me? Am I using the correct type of function?

Leave a Comment or Question