Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smko
Helper I
Helper I

Allocation 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

InvoiceIDDiscount
120
240
350

 

Detail table

InvoiceIDLine itemAllocated discount
1A10
1B10
2A13.33
2B13.33
2C13.33
3A25
3B25

I know how to do it in DAX, but I prefer to do the modeling in Power Query. How do I achieve this?

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@smko 

Please check the attached file. I merged the discount table and added a custom column to divided by the invoice ID count.

Fowmy_0-1626088255119.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@smko 

Please check the attached file. I merged the discount table and added a custom column to divided by the invoice ID count.

Fowmy_0-1626088255119.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


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])
    )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors