Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Remove duplicated rows in SUM calculation

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-_j4QGMIBQq_tPdmY9D8NjL11zcAOBA?e=uzWddg

Need Help

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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