Forum Discussion
Applicable88
Impactful Individual
Impactful Individualuse Values() to get disctinct sum
Hello,
I only want to sum an amount from a distinct orderID, but it always sums up both rows.
My sample table looks like this:
| OrderID | Amount |
| 1234 | 2 |
| 1234 | 2 |
I'm using values to get a distinct row, wrapped in a sumx function:
Measure 16 =
SUMX(VALUES(Table[OrderID]), calculate(sum(Table[Amount)))
or
sumx(Distinct(Table[OrderID]), calculate(sum(Table[Amount)))
Whatever I do I get 4 insead of 2.
But if I would calculate a new table derived from that uderlying table using values() one row would correctly vanish.
What is wrong with that function?
Thank you very much in advance.
Best.
Applicable88 , Try one of the three
Measure 16 =
SUMX(VALUES(Table[OrderID]), calculate(max(Table[Amount)))
or
sumx(Distinct(Table[OrderID]), calculate(average(Table[Amount)))
or
SUMX(summarize(Table[OrderID],Table[Amount), [Amount])
1 Reply
- amitchandak
Super User
Applicable88 , Try one of the three
Measure 16 =
SUMX(VALUES(Table[OrderID]), calculate(max(Table[Amount)))
or
sumx(Distinct(Table[OrderID]), calculate(average(Table[Amount)))
or
SUMX(summarize(Table[OrderID],Table[Amount), [Amount])