Forum Discussion
smko
Helper I
Helper IAllocation of header table into detail table
I want to allocate the discount amount happened at the invoice level into line item evenly. Basically the discount will spread evenly into each item, for example InvoiceID 1 has 2 items, hence 20/2 = 10 discount per item
Header table
| InvoiceID | Discount |
| 1 | 20 |
| 2 | 40 |
| 3 | 50 |
Detail table
| InvoiceID | Line item | Allocated discount |
| 1 | A | 10 |
| 1 | B | 10 |
| 2 | A | 13.33 |
| 2 | B | 13.33 |
| 2 | C | 13.33 |
| 3 | A | 25 |
| 3 | B | 25 |
I know how to do it in DAX, but I prefer to do the modeling in Power Query. How do I achieve this?
2 Replies
smkoThanks Fowmy! I'm new to M, could you explain the meaning of (x)=> and the "x" in front of [Discount] and [InvoiceID]?
(x)=> x[Discount]/ List.Count( List.Select( #"Merged Queries"[InvoiceID] , each _ = x[InvoiceID]) )
