title: Capping a cumulative sum by rank
body: <p>I'm new to PowerPivot/DAX and I'm having some trouble with a specific issue I'm trying to resolve. I have a series of products across multiple stores and need to ship a certain amount from as few stores as possible.</p>
  
  <p>A table <code>Products</code> contains a list of products and the needed amounts:</p>
  
  <pre><code>Product | Need
  0000001 |    7
  0000002 |    8
  </code></pre>
  
  <p>Another table <code>Stores</code> contains the units available by store and I need to calculate how many to send from each store:</p>
  
  <pre><code>Product | Store | Units | Send
  0000001 | 00001 |     5 |    5
  0000001 | 00002 |     2 |    2
  0000001 | 00003 |     1 |    0
  0000002 | 00001 |     0 |    0
  0000002 | 00002 |     3 |    1
  0000002 | 00003 |     3 |    3
  0000002 | 00004 |     4 |    4
  0000002 | 00005 |     2 |    0
  </code></pre>
  
  <p>I have thought of adding a couple of columns for the calculation:</p>
  
  <pre><code>Product | Store | Units |  Rank |  CSum |  Send
  0000001 | 00001 |     5 |     1 |     5 |     5
  0000001 | 00002 |     2 |     2 |     7 |     2
  0000001 | 00003 |     1 |     3 |     8 |     0
  0000002 | 00001 |     0 |     5 |    12 |     0
  0000002 | 00002 |     3 |     3 |    10 |     1
  0000002 | 00003 |     3 |     2 |     7 |     3
  0000002 | 00004 |     4 |     1 |     4 |     4
  0000002 | 00005 |     2 |     4 |    12 |     0
  </code></pre>
  
  <p>First, I rank the stores within each product by units available, solving ties at random:</p>
  
  <pre><code>Rank := IF(Units&gt;0,RANKX(ALL(Stores,Stores[Product]),Stores[Units]+RAND())
  </code></pre>
  
  <p>Then, I calculate the cumulative sum:</p>
  
  <pre><code>CSum := CALCULATE(SUM(Stores[Units]),
      FILTER(ALL(Stores,Stores[Product]),Stores[Rank]&lt;=MAX(Stores[Rank])))
  </code></pre>
  
  <p>Finally, I calculate the amount to send out:</p>
  
  <pre><code>Send := IF(Stores[CSum]&gt;RELATED(Products[Need])+Stores[Units],
      IF(Stores[CSum]&lt;RELATED(Products[Need]),
          Stores[Units],Stores[Units]-(Stores[CSum]-RELATED(Products[Need]))),0)
  </code></pre>
  
  <p>Needless to say, I'm getting <code>#ERROR</code>. I think the thought process works, but the formulas are wrong. Also, my <code>Stores</code> table has ~2M records with ~20k products, will I have any problem running this?</p>