Sum Amount Only Where Customer Number Is Distinct

[PaulMac]

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

[Barthel · Accepted solution]

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.