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:    ...
Need Help
  • Anonymous's avatar
    Anonymous
    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.

     

  • mariusz2022's avatar
    mariusz2022
    4 years ago

    Perfect is working, thanks.

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.

     

    • mariusz2022's avatar
      mariusz2022
      Frequent Visitor
      4 years ago

      Perfect is working, thanks.