Help regarding sum of duplicates

Anonymous — Tue, 13 Jun 2023 08:37:02 GMT

Hi,

I am currently having issues in my current power BI tool. I have 5 similar line items which should be treated individually but since I am calculating the sum of values per month, it is being summed up incorrectly.

Currently this is what it does:

ID	Text	Month	Amount	PeriodSum
002	Hotel	May	300	1800
002	Hotel	May	300	1800
002	Hotel	May	300	1800
002	Hotel	May	300	1800
002	Hotel	May	300	1800
002	Hotel	May	300	1800

Since they are of the same id and text and no actual differences between them, the period sum is being duplicated 5 times as well.

I would like to have the result like this:

ID	Text	Month	Amount	PeriodSum
002	Hotel	May	300	300
002	Hotel	May	300	300
002	Hotel	May	300	300
002	Hotel	May	300	300
002	Hotel	May	300	300
002	Hotel	May	300	300

Is there any DAX formula that might solve this? Currently this is how I am calculating the period sum.

IF('SAP DB'[Functional Area]=5943,

CALCULATE(SUMX('SAP DB','SAP DB'[Amount in local currency]))

All of them are 5943 functional area but I cannot really simply just remove all duplicates in the database because there's a lot of them.

Thanks!

Re: Help regarding sum of duplicates

tamerj1 — Tue, 13 Jun 2023 09:13:15 GMT

Hi Anonymous
Try it with MAXX instead of SMX

Re: Help regarding sum of duplicates

Anonymous — Tue, 13 Jun 2023 09:48:55 GMT

That worked! Thanks a lot!

topic Re: Help regarding sum of duplicates in DAX Commands and Tips

Help regarding sum of duplicates

Re: Help regarding sum of duplicates

Re: Help regarding sum of duplicates