Multiply two columns from different tables, duplicates

[mariusz2022]

Hi, for several weeks I started my adventure with powerbi. I have problem when I want to multiply two columns from different tables and do the sum.

Below are sample tables with the data:

First table is the table with bill of material items, second table is back order table.

I want to check how many BOM_items in Qty is missing in total multyplying by sum of Qty on each Main_Item from BackOrders table.

I try to use NATURALINNERJOIN but there is a problem, because my relation between those two tables can be only many to many, probably the reason are duplicates, but I can't delete duplicates, it comes direct from the postgresql database.

When I made new table using: BOM_Missing_Qty = NATURALINNERJOIN(BOM,BackOrders) , I have information:

Can someone help me or give me some suggestions.

Thanks, with best regards

Mariusz

[v-rzhou-msft · Accepted solution]

Hi @mariusz2022 ,

I suggest you to create a calculated column in "BOM" Table.

Total Missing Qty = 
VAR _SUM = CALCULATE(SUM(BackOrders[Qty]))
RETURN
BOM[BOM_Qty] * _SUM

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

i am not quite sure what you are trying to do can you explain with an example of the actual result of what you are looking to do.

the  many to many relationship on your key will be creating this issue.   Your data sounds like it needs some additional modelling.

if you can explain in clearer terms what you are trying to do with an example and provide your expected result so its clear what you are wanting to come out with, also explaining how the different bom items under one main item needs to be handled.

[mariusz2022]

Dear vanessafvg, there are two tables, BOM and BackOrders. 

In BOM table there are BOM_Item and BOM_Qty which are needed to produce  1pc of each BOM_Main_Item. In BackOrder table are Main_Items and Qty from sales orders which are missing now on the stock. I want to check how many Qty of each BOM_Item is missing.

For example:

In BOM table is Item1, which is produced from Profile1 (1pc) + Profile2 (1pc) +EPDM (6pcs) + Str1 (1pc) + Str2 (1pc)

In table BackOrders is the same Item1 5 times with quantities 15+30+100+43+125 = 313pcs

I want to check how many BOM_Items in Qty are missing, I need to count quantity from yellow column.

In above table do you see:

Profile1 - is missing: 313 pcs
Profile2 - is missing: 313 pcs
EPDM - is missing: 1878 pcs + 924 pcs = 2802 pcs in total
Str1 - is missing: 313 pcs + 308pcs = 621 pcs in total
Str2 - is missing: 313 pcs
Profile3 - is missing: 154 pcs
Profile4 - is missing: 154 pcs

I hope this is clear now.

Thanks

Mariusz

[v-rzhou-msft · Accepted solution]

Hi @mariusz2022 ,

I suggest you to create a calculated column in "BOM" Table.

Total Missing Qty = 
VAR _SUM = CALCULATE(SUM(BackOrders[Qty]))
RETURN
BOM[BOM_Qty] * _SUM

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mariusz2022 · Accepted solution]

Perfect is working, thanks.