cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Sum of a column excluding the duplicates

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...

imbrg

1 ACCEPTED SOLUTION
Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

5 REPLIES 5
Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

HI Mfelix,

can you please tell me why you are deviding by

`CALCULATE ( COUNT ( BP[Unique BLI ID] `

thanks

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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,

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors