Trouble With Links/Formulas

[X3svfdB1HOjKYc0]

I have two tables. The first table is a list of containers, charge codes and amounts. See sample table with charges totalling 1400.

The second table is a list of containers, order lines and physical volume. There is a many-to-many relationship between these tables.

I want to setup a formula that will allocate the freight costs pro rata based on the volume as show below.

I just can't seem to get the bridge table and formulas right for this. Any help would be very much appeciated.

I also want to set this up in a way where I can link addition information to the container (many from this results table to a single key in the container table) and also additional order information (again, many from this results table to a single key in the order table). I don't think this will be an issue but just wanted to make sure that any solution keeps this in mind.

[X3svfdB1HOjKYc0]

Thanks for your help getting the inital table merged. This is already a big step forward. 

Edited and updated from earlier post. I am now getting a result that is not an error, however the values seem to be divided by the volume an extra time.

I could take the result and multiply it again by the volume to force the correct solution, but, is there a more elegant way to get the original formula?

[Anonymous]

Hi @X3svfdB1HOjKYc0,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@X3svfdB1HOjKYc0 , Merge these two tables in power Query

 : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

a new column in DAX =

divide([Amount] , sumx(filter(Table, [Container $] = earlier( [Container $]) && [Charge] = earlier( [Charge]) ), [Volume] ))