Calculating ratios of multiple items in an order and then calculate discount

[aJ2]

Hi.

I have table where I have id and order_id where id is the serial number. 

An order_id can have multiple items.

An order can have a discount amount with it ie at checkout, the person uses a discount code. 

I need to calculate the discount for each item in that particular order.

eg:- there are 3 items in an order, item A has a value 100, item B 200 and item C 300 respectively. A coupon code is used and the coupon value is 120. So the ratio between the 3 items are taken ie the ratio is 1:2:3. 

The equation becomes 1x+2x+3x= 120. => 6x=120 => x=20.

So the discount for item A is 20, Item B 40 and item C 60.

Above is the snippet of sample data.

Kindly help.

[Floriankx · Accepted solution]

Hello,

so you have a separate table with order_id and discount?

If you relate your order_id columns this does the job as Calculated column:

=VAR OrderID=[order_id]
VAR whole_order=CALCULATE(SUM(Table[cost]);FILTER(Table;Table[order_id]=OrderID))
RETURN Table[cost]/whole_order*RELATED(Discount[discount])

[aJ2]

Yes. I had a separate table for discount and order_id. But then there was another table where an order_id was associated with a voucher code. 
So I used RELATED() and got the details for the coupon codes and then carried out the calculations.

Thank you so much for your help.