Forum Discussion

PaulMac's avatar
PaulMac
Helper IV
4 years ago
Solved

Sum Amount Only Where Customer Number Is Distinct

Hi

Here is my sample data:

Customer Number

Amount
AA110
AA225
AA225
AA225
AA225
AA3100
AA110
AA110
AA110
AA110
AA110
AA110
AA110
AA110
AA110

 

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

  • Barthel's avatar
    Barthel
    Solution 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.