Sum of a column excluding the duplicates

[Anonymous]

Hello

I need to sum the "total Amount" excluding the duplicates. to remove the duplicates, actually I need to use only the unique to take into consideration the rows where the unique values of the the column "Unique BLI ID". 

I am using the following measures (supposed to give the same results) but none of them is working

Bookings ;=SUMX(FILTER(BP,DISTINCT(BP[Unique BLI ID])),BP[Total Amount])

Total Bookings ;=CALCULATE(SUM(BP[Total Amount]),FILTER(BP,DISTINCT(BP[Unique BLI ID])))

Here is the link to the Excel file

https://laurentide0-my.sharepoint.com/:x:/g/personal/ibourega_laurentide_com/EVMldwoOdqFGpwr-_j4QGMI...

thank you for your support

imbrg

[MFelix]

Hi @Anonymous,

Looking at you data the UNIQUE BLI ID has always the same amount why don't you simplify and make this measure:

=SUMX (
    DISTINCT ( BP[Unique BLI ID] );
    CALCULATE ( SUM ( BP[Total Amount] ) )
        / CALCULATE ( COUNT ( BP[Unique BLI ID] ) )
)

Believe it is getting the correct result but please check since you have a very extense list of unique id.

Regards,

MFelix

[Anonymous]

HI Mfelix,

can you please tell me why you are deviding by 

CALCULATE ( COUNT ( BP[Unique BLI ID] 

thanks

[MFelix · Accepted solution]

Hi @Anonymous,

My measure is calculating the full sum of the BLI ID and then then dividing by the number of times it shows up. the first part is to only make a single sum this result per BLI ID other wise it would give you the same result you already have.

So basically I'm calculating the amount and making the sum of each ID.

Regards,

MFelix

[Anonymous]

Hello,

can you please tell me which of the following measure is the most optimised 

Bookings V!:=SUMX (

  DISTINCT ( BP[Unique BLI ID] ),

    CALCULATE ( SUM ( BP[Total Amount] ) )

        / CALCULATE ( COUNT ( BP[Unique BLI ID] ) )

)

Bookings V2:= =SUMX(

  SUMMARIZE(

    'BP',

    'BP'[Unique BLI ID],

    'BP'[Total Amount]

  ),

  'BP'[Total Amount]

)

regards,

[MFelix]

Hi @Anonymous,

After giving you the response and closing my computer I was thinking that the summarize option could also work and was going to go back to place the summarize as an option, good to see you got there.

Not really sure what would be the best optimization because depends on your data model, however you can check the tool below that allows you to test out DAX performance.

https://www.sqlbi.com/tools/dax-studio/

Regards,

MFelix