Forum Discussion

caito103's avatar
caito103
Icon for Helper I rankHelper I
9 years ago
Solved

Multiply two columns from different tables with a relationship

Hello,

I have a few days working with PowerBI, and im here again asking for help.

I need to multiply two columns ("Cantidad" per "Litros") from two differents tables, but this multiply have to be one cell by one cell where each one need to be associate trought other columns (Producto and Cod.Producto, are the same IDs (or key) but in differents tables ).

 

FIRST TABLE

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SECOND TABLE

 

 

I tried with the following code:

 

Cajas = CALCULATE(SUMX(Hoja2[Cantidad]*USERELATIONSHIP(Hoja2[Producto];Prod[Cod. Producto])))

 

Can be this solved with CROSSJOIN, INNERJOIN or something similar? Im really in troubles.

 

Thank you so much!

 

 

 

 

Need Help

7 Replies

  • cs_skit's avatar
    cs_skit
    Icon for Resolver IV rankResolver IV
    9 years ago

    New Column for first table:

     

    CantitadLitros = FirstTable[Column2] * RELATED(SecondTable[Column2])

     

    correct for your table and column names I did different ones

     

    key is RELATED functionality

    can't be used everywhere if you want it in a measure you need FILTER() DAX thats why I chose calculated column here

     

    you need relationship on productid between the two tables of course for this to work

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    Hi caito103,

     

    Assume Producto and Cod.Producto has relationship n:1 (or 1:1) then you can create a relationship between those two tables based on Producto and Cod.Producto. See: Create and manage relationships in Power BI Desktop.

     

     

    Then create a measure like below:

    Measure = SUM(Table1[Cantidad])* SUM('Table2'[Litros])

     

    Another way is in Query Editor, use Merge Queries to merge those two tables to one table "Merged", see: Append vs. Merge in Power BI and Power Query, then create a measure  like below:

    Measure2 = SUM(Merged[Cantidad])* MAX('Merged'[Litros])

     

    Please see attached .pbix file.

     

    Best Regards,
    Qiuyun Yu

    • erwinpm's avatar
      erwinpm
      Icon for Microsoft Employee rankMicrosoft Employee
      4 years ago

      This answer may not provide the desired result:

      SUM(A * B)

      is not the same as

      SUM(A) * SUM(B)

      What you need to do is:

      • Either merge the tables in PowerQuery, and then use SUMX to calculate the product, 

       

      SUMX(MergedTable, [Cantidad]*[Litros])

       

      • Or create a relationship in the Model view and then us NATURALINNERJOIN to join the tables and SUMX to calculate the product.

       

      SUMX(NATURALINNERJOIN(Table1,Table2), [Cantidad]*[Litros])

       

      • JoachimHaas's avatar
        JoachimHaas
        New Member
        4 years ago

        Thanks a lot for this tip, was confronted with a similar task, used the NATURALINNERJOIN formula, works perfectly! 🙂