Remove duplicated rows in SUM calculation

[Anonymous]

Hello

I need to calculate a total of  a column but there are duplicate values that I need to get rid of them

the two following measures are not working.

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

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

I need to compute the total amount of booking based only taking into consideration the unique vlaue of the column [Unique BLI ID]

please support 

please find below the link to the Excel file

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

[v-piga-msft]

Hi @Anonymous,

There are two way for you to achieve your requirement.

1. Remove duplicate rows in Query Editor and then apply and close to report view, create a measure with the fomrula below.

Measure = CALCULATE(SUM('remove duplicate rows'[Amount]))

2. Create the measure directly with Dax formula below.

Measure 2 =
SUMX (
    SUMMARIZE ( 'Table1', 'Table1'[ID], "amount", AVERAGE ( Table1[Amount] ) ),
    [amount]
)

In addition, you also could refer to my test pbix.

Best  Regards,

Cherry