Forum Discussion

Helper II

5 years ago

Solved

Sumx with Distinct Value

Hi Everyone

🙂

not sure if anyone could help me

currently, i am working on the total weight of below table

but you can see some of the File Ref is duplicate, i just need the weight of Distinct File Ref

so, i wrote...

DistinctTotalWeight = SUMX(DISTINCT(VALUES(TableName[File Ref])),CALCULATE(SUM(TableName[Weight])))

but this is not work, do you have any idea or any thought?

Thank you.

File Ref is TEXT format

Weight is Number format

File Ref	Weight
2750484565	3752.5
2750484569	74.5
2750484569	74.5
2751197873	752
2751255824	2310
2751256026	28.5
2751256026	28.5
2751261366	880.5
2751290490	351.5
2751294283	1240
2751294284	1537.5
2751294285	332
2751306434	888.5
2751306434	888.5

CNENFRNL
5 years ago
Hi, Ericwhv , you might want to try this measure
SUMX( DISTINCT( Table1[File Ref] ), CALCULATE( MAX( Table1[Weight] ) ) )

4 Replies

CNENFRNL
Community Champion
5 years ago
Hi, Ericwhv , you might want to try this measure
SUMX( DISTINCT( Table1[File Ref] ), CALCULATE( MAX( Table1[Weight] ) ) )
- ed185
  Regular Visitor
  4 years ago
  Hi,
  Does anyone know what is the difference between :
  SUMX( DISTINCT( Table1[File Ref] ), CALCULATE( MAX( Table1[Weight] ) ) )
  and
  SUMX( DISTINCT( Table1[File Ref] ), MAX( Table1[Weight] ) )
  
  Both don't lead to the same results in my example.
  Thanks.
Ericwhv
Helper II
5 years ago
CNENFRNL
Thank you for your help it is work now! 🙂
if you have time, could you mind to tell me why we use "MAX" instead of "SUM" here?
thank you so much!
- CNENFRNL
  Community Champion
  5 years ago
  Ericwhv , glad that my solution is of help. Further to your question, if SUM() is used, all weights of the same a unique File Ref will be summed up. You might want to try these 2 measures to see the differences.
  Test MAX = SUMX( DISTINCT( Table1[File Ref] ), CALCULATE( MAX( Table1[Weight] ) ) ) Test SUM = SUMX( DISTINCT( Table1[File Ref] ), CALCULATE( SUM( Table1[Weight] ) ) )