Looking for help with in determining total units and matching to cost of goods sold in Excel

[JoePaulTX]

Link to sample excel I made: https://1drv.ms/x/s!Ahy9ZYzKXjd9gdo2UBseB0q0WrYbiw?e=IeerZX

This is a sales report that generates 4 rows for each transaction. And puts the total units sold in each row. For example, I circled the first transaction in which 1 item was sold of Widget 1 but there are four entries in the "Ship Qty" column.

I want to create another sheet with 3 columns. "Partner Item Id", "Total units sold", and "Total Cost of Goods" for my accounting and I cannot figure out how to extract just the shipped units from maybe the "Product Price" row only.  I will then match those units with my COGS (cost of goods sold) sheet to figure out my total COGS for this report date.

Any help would be appreciated. Thank you

[Manoj_Nair]

 

@JoePaulTX - try this DAX and let me know if this works. Many Thanks