How to count individual product price in order?

[karolisarb]

I have a bunch of orders with products in a list. Two products, marked red, are displayed in separate rows but products are the same. The quantity did not not sum of those two products in database, but the total price is summed.

Problem is that total price shows the total sum of same two products. And if there are more than one same product in order displayed in separate row, those rows total price sums together.

Can anyone help me with filters? If in an order there is more than one of the same product displayed in separate row, I want that the total price won't be calculated between them.

I need another column, were price is calculated in this way: if there is more than one product with same product code in same order (order id) then the total price should be devided by quantity of sum of same products

101023 | 2017-03-06 | 7610917627158 | 1 | 14.86 | 7.43 (new column)

101023 | 2017-03-06 | 7610917627158 | 1 | 14.86 | 7.43 (new column)

[dataloreous]

I would first try to change the query from the source to only return distinct records. If that does not work or you do not have control over the source use the 'Remove Duplicates' option to add a step to the query. That will leave you with unique records in your query which can then be summed easily.

Reduce Rows --> Remove Rows --> Remove Duplicates

[karolisarb]

I think 'Remove duplicated rows' will remove the quantity

[dataloreous · Accepted solution]

You could also try adding a group by on the Order, Product ID, and Amount then using aggregations on your quantity and total amount.

If this is your starting dataset, with the top two records the same product from the same order add a group by like below, grouping on order, product, and amount.

 

You will end up with the dataset below, where you can then easily add a new column that divides the unique amount by the total quantity:

The final result would be like below:

[karolisarb]

Thank you! It works