calculate product cost twice

[Xueli_Ji]

Hi everyone,

I want to calculate the profit which is equal to [total] minus [product cost]. In the original table, there are two data raws, but I found that Power BI minus the product cost twice, and I don't know why. How can i optimize my formula?

other information：

total: 'payment'[total], which is a column of the table of payment

productCost: CALCULATE(SUMX(payment,(payment[purchaseCost] + payment[shippingFee])*(1+payment[tarrifRate]/100)*payment[quantity]), payment[type] = "Order"), which is a measurement

profit: SUMX(payment,payment[total] - [productCost])

BEST REGARDS

[AntrikshSharma]

Simply remove duplicate orders?

[Xueli_Ji]

I just hide some columns, in fact, the two raws are different, and they are not "wrong" records, I can't remove any one of them.

BEST REGARDS

[Anonymous · Accepted solution]

In Power Query, 

Just highlight "Order ID" column. Go to home> remove rows> Remove duplicates? 

If you actually need to use the duplicated data for other expressions, I'd recommend right clicking on the data set on the queries pane, then going to reference. This will allow you to retain the original (with duplicates) while creating a mirror copy without duplicates. Then you can just create a relationship between them both using Order ID. 

[Xueli_Ji]

Thank you! I take the suggestion to make a copy.  But I still wonder why the [profit] will minus the product cost twice?