Forum Discussion

mariusz2022's avatar
mariusz2022
Frequent Visitor
4 years ago
Solved

Multiply two columns from different tables, duplicates

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

Need Help

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    4 years ago

    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's avatar
      mariusz2022
      Frequent Visitor
      4 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.