How to sum distinct values only

[o59393]

Hi all

I am trying to sum only distinct values for the column "units" where the combination is the same for month+code+city

As seen for example:

In the image above it should only sum 35,661 once.

I tried this dax but unsuccesful result:

Sum RE distinct = 

SUMX(
SUMMARIZE(Sheet1,Sheet1[Code],Sheet1[City],Sheet1[Month]),
MAX(Sheet1[Units])
)

Result should be:

https://1drv.ms/u/s!ApgeWwGTKtFdhyxvwe8MeK5wIhZ0?e=CfKDy4

How can I get it right?

Thanks.

[amitchandak · Accepted solution]

@o59393 , a new measure

sumx(summarize(Table,Table[month],Table[code],Table[city],Table[unit]),[unit])

or your formula like

Sum RE distinct = 

SUMX(
SUMMARIZE(Sheet1,Sheet1[Code],Sheet1[City],Sheet1[Month],"_1",MAX(Sheet1[Units])),
[_1]
)

[Anonymous]

Can some one explain why this works?