Multiply two columns from different tables with a relationship

[caito103]

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!

[v-qiuyu-msft · Accepted solution]

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]

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])

[abtm]

I spent two hours trying to fix my issue and your solution was the key. Thank you, Legend.@erwinpm 

[backflash]

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.

[DemandMGR]

Thank you for this. I was trying to calculate two values from two different tables and this worked perfectly!

[JoachimHaas]

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

[cs_skit]

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