Need help multiplying 2 columns from different tables using a many to many relationship

[airwolf39]

Hi all,

I am trying to take ordered quantities (Balance [Factory Units] column)  in one table and multiply those quanitites by the Child part quantities in another table and sum up the child part quanities. As you can see below the totals are wrong and things really break when trying to analyze at the Child Part level (which is the goal):

I am forced to use a many-to-many relationship because the only column that these 2 tables share are "FG Part#":

Bill of materials table (no 1:1 value as child parts can be shared in multiple FG Part #s):

Sales orders table (no 1:1 value as each SO can order multiple FG Part #s):

Is what im trying to do possible? Any tips would be helpful. Thanks

[airwolf39 · Accepted solution]

I was able to figure out a solution. The solution was to merge these 2 tables using a full outer merge in query editor tied to FG part number. I then created a key using the SO#+FG# in both the merged table and the sales orders table. Now i can slice and dice to my hearts content.

[airwolf39]

After thinking it over more, it could be that the SO# slicer doesnt work because the Calcuate function is wiping the filter context? When i use just a sumx i get this... which doesnt make sense because my tables have a valid relationship?:

[chris_moore_pf]

It's doing what your measure tells it to, i.e. 13 * 5.11 = 66.44. Can you add a column with DAX LOOKUPVALUE to 'move' over the correct info or M Language merge queries?

[airwolf39]

@chris_moore_pf i tried that also. It works at the aggregate level, but if i slice by SO, then it doesnt work. Maybe my calculated column formula is wrong?:

Aggregate is ok:

Slice by SO# is not ok (2x1 is 2, not 16). The reason this is happening is because the relationship is by FG#. And when the SO is sliced, the bill of materials formula still pulls all the SO's that are buying that FG#. I need a way for the bill of materials table to only calculate the FG quanitities of the SOs that are being sliced:

lookup formula in bill of materials table:

[Johanno]

Havn't tried, but you should in pseudo code use something like:

SUMX( the table you want to iterate (Bill of materials?)  ,   the column from Bill of materials   *   RELATED ( the column in the related table ) )

Does this make sense? 🙂

[airwolf39]

That is what i wanted to do @Johanno but every time i try to use 'related' its greyed out. I think its because there is not a relationship between Child Quantity and Order Quantity?: 

[Johanno]

Hi, using many to many relationships can give you strange results and even if it works today it can be dangerous making changes in the future, so you really should think about rebuilding the model. Could you instead create a table with distinct FG Part# between the two tables? I think sharing the PBIX file or a screen dump of the data model would help.

[Greg_Deckler]

@airwolf39 - First, I would avoid many-to-many relationships if possible and use a bridge table. Second, could be more specific if you posted sample data as text so your situation could be mocked-up easily. Expected output is always helpful as well. 

[airwolf39]

Hi Greg,

I executed on your suggestion by making a bridge table but the values are still the same. Perhaps my Dax formula is wrong? The correct total should be around 2.8m units :