Forum Discussion

caito103's avatar
caito103
Helper 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 o...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    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
Microsoft 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])

 

backflash's avatar
backflash
Helper II
4 years ago

Thanks erwinpm - I had quite a similar problem and was not satisfied with my measures so far as the row-totals in a matrix have been wrong while the rows were correct.
With using NATURALINNERJOIN this works fine now.