March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
Solved! Go to 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
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
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:
SUMX(MergedTable, [Cantidad]*[Litros])
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! 🙂
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |