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

cs_skit's avatar
cs_skit
Resolver 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