SUMX DISTINCT PowerPivot not working as expected

Question

I have data like this returned from a SQL query:

REF   ORDER   R_UNITS   R_STOCK   COMPONENT
-------------------------------------------
TOY1   SP1      500       350      BLISTER
TOY1   SP2      400       350      BLISTER    
TOY1   SP3      100       350      BLISTER    
TOY2   SP1      200       200      BLISTER   
TOY2   SP4      300       200      BLISTER    
TOY3   SP1      600       300      BLISTER     
TOY3   SP1      400       300        BOX

And I would like to pivot it like this into an Excel Pivot Table:

FILTER ORDER: ALL

COMPONENT   R_UNITS   Σ R_STOCK
BLISTER      2100        850  
BOX          400         300    

R_STOCK should iterate over the data and summarize results based on unique REF values.

I tried with this DAX formula:

Σ R_STOCK:=SUMX(DISTINCT(Table6[REF]),AVERAGE(Table6[REF STOCK]))

But instead of summarizing each unique value (300+350+200=850) for BLISTER, it does the AVERAGE of the total of all the rows and then it multiplies the result for the number of unique values: (350+350+350+200+200+300)/6*3=875

How can I fix my DAX formula?

Thank you very much

Answer 1

I think we need to use SUMMARIZE to get the distinct pairs of REF and REF STOCK, and then to add the REF STOCK. I'd also add a filter modifier to remove any possible filter apart from the one on COMPONENT

R_STOCK :=
CALCULATE(
    SUMX( SUMMARIZE( Table6, Table6[REF], Table6[REF STOCK] ), Table6[REF STOCK] ),
    ALLEXCEPT( Table6, Table6[COMPONENT] )
)