Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SUMX avoiding duplicates?

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[LC...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Try like

 

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