How do I SUMIF one PowerPivot table according to the rows of a second PowerPivot table?

Question

I have two tables: one of customers ("Donor"), and one of transactions ("Trans"). In Donor, I want a "Total" column that sums all the transactions by a particular Donor ID, which I would calculate in a standard Excel table thus: ``` =SUMIF(Trans[Donor ID],[@ID],Trans[Amt]) ``` Simple! How do I do the same thing with a DAX formula? I thought ``` =CALCULATE(SUM(Trans[Amt]),Trans[Donor ID]=[ID]) ``` would do it, but I get the error ``` Column "ID" cannot be found or may not be used in this expression. ``` Strangely, when I use ``` =CALCULATE(SUM(Trans[Amt]),Trans[Donor ID]=3893) ``` I do get the total for ID 3893. Eschewing CALCULATE, I did find that this works: ``` =SUMX(FILTER(Trans, Trans[Donor ID]=[ID]),[Amt]) ``` ...but it only allows the one filter, and I'll need to be able to add more filters, but: ``` =SUMX(CALCULATETABLE(Trans, Trans[Donor ID]=[ID]),[Amt]) ``` ...(which I understand is like FILTER but allows for multiples) does not work. Can you identify what I'm doing wrong?

Accepted Answer

After putting together a quick model that looks like this:

I've confirmed that this DAX forumla works as a Calculated Column in the Donor table:

```
=CALCULATE(SUM(Trans[Amt]), FILTER(Trans, Trans[Donor] = Donor[DonorKey]))
```

The key here is to make sure that the relationship between the two tables is correctly configured, and then make sure to use the combination of CALCULATE() and FILTER() -- filtering the trans table based on the current donor context.

How do I SUMIF one PowerPivot table according to the rows of a second PowerPivot table?

Solution

Problem