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[LCSP]),[MDMxSize])
 
How can I do a SUMX but avoiding duplicates?
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

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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