Sum of Margin where there are multiple line items not working

[Jitmondo]

Hi

I have a table where I am trying to sum up margin at a unique level.

The table has unique sales ids but can have multiple products so has duplicate lines.The problem here is that the margin is locked at a sale id level so is duplicated, I need the margin for each product but I end up getting duplicate values included in my sum!

I have used 

Distinct Margin = (SUMX(DISTINCT(Opportunity[Id]), FIRSTNONBLANK(Opportunity[Margin Conversion], 0)))

which is almost there however if a sale id has multiple products it is not included in the calculation.

Made Up ID	Margin	Price	Product
1245	4510	300	A
1245	4510	200	B
1245	4510	100	C
Result now	Desired Result
Product	Margin	Product	Margin
A	13530	A	4510

Any ideas on where I am going wrong ?

thanks

[Jitmondo]

Turns out my formula was correct and I had double counted (facepalm), however your formula worked!!! thanks again 🙂

[amitchandak · Accepted solution]

@Jitmondo , Try measure like

Distinct Margin = SUMX(values(Opportunity[Id]), FIRSTNONBLANKVALUE(Opportunity[Made Up ID], sum(Opportunity[Margin Conversion])))

[Jitmondo]

Thanks for coming back to me, unfortunately it didnt work, that formula allows one of the product categories (the one I mentioned having duplicates) to double count. Not sure why its doing that unless there is something else I have missed.