Calculate margin from multiple rows having different values in common

[DiegoMadr]

Dear Forum,

First of all, thank you very much for reading my help request!

Background: we exported a big json file with +40K rows containing information about profit and losses of a translation company. Sometimes, we have several customer invoice and purchase order rows for the same translation job: there is a column for the source language and another one for the target language. On top of this, each translation job belongs to a project number. 

The question: I need to calculate the gross margin of these multiple rows (ones being customer invoice and others being purchase order) if they match with the same project number, source and target language. Once this calculation is done, I believe I need another one to calculate the overall gross margin of the project as a whole, right? Or can Power BI get the right gross margin with the first calculated margin?

I leave here a screenshot of the report itself where you can see the project number on the left, the "converted_amount" on the right and the source and target language columns at the far end. In the red boxes you can see that we have two rows for the same source and target language under the same project number. Therefore, first I guess that the values must be summed if they meet the criteria and secondly the margin can be calculated within the project. 

I have been thinking about how to get this calculation done for the last three weeks, but it is definitely too complex for me and I need help with it... 

Thank you very much in advance!

Diego

[v-luwang-msft]

Hi @DiegoMadr ，

could pls provide your pbix file ? And how to calculate the gross margin.Is there a formula for it? Remember to remove the confidential information.

Best Regard

Lucien Wang