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.

Reply
caito103
Helper I
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

qBI1jSBiTp6ZVqeDS4c0iQ

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SECOND TABLE

 

4o9kvMTGQdOpN7_HnHPURg

 

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
v-qiuyu-msft
Community Support
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.

 

q1.PNG

 

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.

View solution in original post

7 REPLIES 7
v-qiuyu-msft
Community Support
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.

 

q1.PNG

 

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.

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

 

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

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.

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

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

cs_skit
Resolver IV
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.