The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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.
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
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?
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
7 |