Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## 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!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Employee

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

Regular Visitor

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

Helper II

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.

Helper I

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

New Member

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

Resolver IV

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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors