Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Xueli_Ji
Regular Visitor

calculate product cost twice

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])

WXWorkCapture_15954105699553.png

BEST REGARDS

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

View solution in original post

4 REPLIES 4
AntrikshSharma
Super User
Super User

Simply remove duplicate orders?

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
Not applicable

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. 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.