How do I SUMIF one PowerPivot table according to the rows of a second PowerPivot table?
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?
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.