Forum Discussion
Sum Amount Only Where Customer Number Is Distinct
Hi
Here is my sample data:
Customer Number | Amount |
| AA1 | 10 |
| AA2 | 25 |
| AA2 | 25 |
| AA2 | 25 |
| AA2 | 25 |
| AA3 | 100 |
| AA1 | 10 |
| AA1 | 10 |
| AA1 | 10 |
| AA1 | 10 |
| AA1 | 10 |
| AA1 | 10 |
| AA1 | 10 |
| AA1 | 10 |
| AA1 | 10 |
The Amount column is a repetative number and only value is needed but the dataset is provided as above.
What I am trying to accomplish:
I want to be able to produce a total amount that the customer has but only where the Customer Number is distinct. So if a Customer Number appears more than once I want to only include one amount value and disregard the remaining ones.
With regards to the table above, the correct amount total should be 135 but every DAX measure i try to produce will only ever show a total of 300.
Here is the code I thought would be sufficient but it is not working as I expected.
Distinct.Sum = CALCULATE( SUM( Table1[Amount] ) , DISTINCT( Table1[Customer Number] ))
As always, any assistance is greatly appreciated.
Paul
Hey,
Give this a try:
Distinct.Sum = SUMX ( KEEPFILTERS ( VALUES ( Table1[Customer Number] ) ), CALCULATE ( SELECTEDVALUE ( Table1[Amount] ) ) )It might also be an idea to remove the duplicate values in Power Query, if you don't use them in your report yet. Then you can just take the SUM.
1 Reply
- BarthelSolution Sage
Hey,
Give this a try:
Distinct.Sum = SUMX ( KEEPFILTERS ( VALUES ( Table1[Customer Number] ) ), CALCULATE ( SELECTEDVALUE ( Table1[Amount] ) ) )It might also be an idea to remove the duplicate values in Power Query, if you don't use them in your report yet. Then you can just take the SUM.