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 A...
Need Help
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