title: How do I SUMIF one PowerPivot table according to the rows of a second PowerPivot table?
body: <p>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:</p>
  
  <pre><code>=SUMIF(Trans[Donor ID],[@ID],Trans[Amt])
  </code></pre>
  
  <p>Simple! How do I do the same thing with a DAX formula? I thought</p>
  
  <pre><code>=CALCULATE(SUM(Trans[Amt]),Trans[Donor ID]=[ID])
  </code></pre>
  
  <p>would do it, but I get the error</p>
  
  <pre><code>Column "ID" cannot be found or may not be used in this expression.
  </code></pre>
  
  <p>Strangely, when I use</p>
  
  <pre><code>=CALCULATE(SUM(Trans[Amt]),Trans[Donor ID]=3893)
  </code></pre>
  
  <p>I <em>do</em> get the total for ID 3893.</p>
  
  <p>Eschewing CALCULATE, I did find that this works:</p>
  
  <pre><code>=SUMX(FILTER(Trans, Trans[Donor ID]=[ID]),[Amt])
  </code></pre>
  
  <p>...but it only allows the one filter, and I'll need to be able to add more filters, but:</p>
  
  <pre><code>=SUMX(CALCULATETABLE(Trans, Trans[Donor ID]=[ID]),[Amt])
  </code></pre>
  
  <p>...(which I understand is like FILTER but allows for multiples) does not work.</p>
  
  <p>Can you identify what I'm doing wrong?</p>