Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
@smko
Please check the attached file. I merged the discount table and added a custom column to divided by the invoice ID count.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@smko
Please check the attached file. I merged the discount table and added a custom column to divided by the invoice ID count.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks 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])
)