SUMX avoiding duplicates?

[Anonymous]

I have this data:

And I get the wrong value, I aim to get  (50*1 + 35*1,4)  just once; that is 99 and not 148...

My last measure is 

MDMxSizePerLCSP =

SUMX(ALLEXCEPT(Sheet1,Sheet1[LCSP]),[MDMxSize])

How can I do a SUMX but avoiding duplicates?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Check the formula.

Measure = 
VAR _AVG1 = AVERAGEX(ALLEXCEPT(Sheet1,Sheet1[LCSP],Sheet1[CUST]),[MDMxSize])
VAR _COUNT = CALCULATE(COUNT(Sheet1[CUST]),ALLEXCEPT(Sheet1,Sheet1[LCSP],Sheet1[CUST]))
VAR _AVG2 = _AVG1/_COUNT
RETURN
_AVG2

Measure 2 = SUMX(ALLEXCEPT(Sheet1,Sheet1[LCSP]),[Measure])

Best Regards,

Jay

[amitchandak]

@Anonymous , Try like

MDMxSizePerLCSP =
calculate(SUMX(values(Sheet1[cust]) ,[MDMxSize]),ALLEXCEPT(Sheet1,Sheet1[LCSP]))