title: How to SUM DISTINCT in a calculated field for pivot table
body: <p>I have data like this coming from an SQL query:</p>
  <pre><code>REF   ORDER   R_UNITS   R_STOCK   COMPONENT   C_NEEDED   C_STOCK   HELPER
  TOY1   SP1      500       350      BLISTER       500       500        1
  TOY1   SP2      400       350      BLISTER       400       500        1
  TOY1   SP3      100       350      BLISTER       100       500        1
  TOY2   SP1      200       200      BLISTER       200       500        1
  TOY2   SP4      300       200      BLISTER       300       500        1 
  TOY3   SP1      600       300      BLISTER       600       500        1  
  TOY3   SP1      400       300        BOX         400       300        1
  </code></pre>
  <p>And I would like to pivot it like this:</p>
  <pre><code>FILTER ORDER: ALL
  
  COMPONENT   R_UNITS   Σ R_STOCK   Σ R_TO_PACKAGE   C_STOCK    Σ C_TO_BUY
  BLISTER      2100        850          1250           500         750
  BOX          400         300          100            300          0
  </code></pre>
  <p><strong>R_STOCK</strong> should be a calculated field iterating unique values over the data and summarising the results: 350 TOY1 + 200 TOY2 + 300 TOY3 = 850</p>
  <p>I think PowerPivot SUMX would do the job, but my Excel is not compatible with PowerPivot</p>
  <p>I cannot group my SQL query by REF and COMPONENT because I'd like to add a filter with ORDER to my pivot table, so it shows different results based on the order selected (SP1, SP2...).</p>
  <p>I tried pretty much everything that came to my mind. I tried to divide R_STOCK by count(REF) with SQL. But then it returns a non variable value (for example 850/3 for TOY1, which is correct if I don't filter), but I want it to vary depending on the number of rows (affected by the ORDER filter added to the pivot table).</p>
  <p>I don't know if I'm explaining myself. Ask me if I need to clarify anything</p>
  <p>PD: I can add new columns with SQL or PowerQuery if needed.</p>