Sum column number based on distinct values of other column

[Chetcork]

Hi,

I would like to sum a column excluding duplicate values. I don't want to remove duplicate values form data model.

My table looks like this:

date	value	inv no	payment date	paid
23.09.2022	5000	inv/2/2022	29.09.2022	0
22.09.2022	5000	inv/2/2022	29.09.2022	0
21.09.2022	5000	inv/2/2022	29.09.2022	0
21.09.2022	6000	inv/2/2022	29.09.2022	0
20.09.2022	6000	inv/2/2022	29.09.2022	0

"Value" column and "inv no" column is repeated till the payment date. I would like caluclate sum of values only for distinct "inv no", so the result in this case should be 11000. My visualisations requieres two type of information first one is quite easy i just need sum of all amount of "value" column without filtering, but the second part is more difficult for me. I've already tried using distinct in filter, but result were the same.

I do hope you guys have any idea.

Thank you and appriciate all your help.

[parry2k · Accepted solution]

@Chetcork try this measure:

Sum = 
SUMX ( SUMMARIZE ( Table, Table[InvNo], "@Value", MAX ( Table[Value] ) ), [@Value] )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.