Allocate discount across invoice line items

[Anonymous]

Hi All 

I hope someone can help. I have a table from Xero with invoice data, each invoice is listed with seperate rows for each line item including the discount. I would like to be able to apportion the discount across each line based on the LineItem_LineAmount so that the true value of each line item can be seen.  Below is an example invoice where I have calculated the discount apportionment but which I am not able to do in Power Bi as a calculated column.  The discount should be the only line with a negative value.

If anyone could help it would be much appreciated. 

Thanks

Jack 

Id	LineItem_Description	LineItem_LineAmount	InvoiceNumber	Discount flag	Discount calc
6|9d11a032-bcd2-48bc-bde5-a62d5dc965ed	Discount (custom discount)	-59.14	EB18237	1	59.14
4|9d11a032-bcd2-48bc-bde5-a62d5dc965ed	Starter Fasteners - 100	13.49	EB18237	0	-0.67444
1|9d11a032-bcd2-48bc-bde5-a62d5dc965ed	10-40mm Riser - 10mm	164.91	EB18237	0	-8.2448
5|9d11a032-bcd2-48bc-bde5-a62d5dc965ed	Bearer - 50x50mm - 3.1m	222.84	EB18237	0	-11.1411
2|9d11a032-bcd2-48bc-bde5-a62d5dc965ed	Slate - 4m	733.68	EB18237	0	-36.6809
3|9d11a032-bcd2-48bc-bde5-a62d5dc965ed	6mm Fasteners - 100	47.98	EB18237	0	-2.3988

[v-frfei-msft]

Hi  @Anonymous ,

Is the discount column your excepted result? I cannot get the logic of that, Could you please share more details about that?

[Anonymous]

Hi 

Yes the discount column is the expected result.

For each invoice the disount is removed (so that for the row with disount adding line item value and discount comes to zero), the dscount is then allocated across the line items (pro rated  by line item value) so that the total of the Disocunt column for any single invoice is zero but each line items row now has an allocated discount credit in that column.

I hope that makes sense?

Thanks 

Jack

[Anonymous]

 @v-frfei-msft 

Is there any more helpful information i can provide or a better way of laying it out? 

To try to summarise again for any group of rows in the table with the same invoice number, where there is a negative line item amount I want the calcaulted discount column to reverse it and apply the credit to the remaining lines in proportion to the line item amount.

I've also tried to simplify the original table.

Thanks 

Jack