Forum Discussion
Resolver IMultiply columns row by row from two different tables and get correct sum
Hi, row operations like multiply seem a bit tricky in PowerBi to get the correct sums especially.
I would be grateful if someone could help on an efficient way to accomplish this effectively.
For the example multiplying
- [row column} By [row column] in same table
- [row column] by [row column] in different tables
- [row column] by [measure] in same table
- [row column] by [measure] in different tables
- [measure] by [measure] in same table
- [measure] by [measure] in different tables
It seems many of the basic expressions break when combining two different "types" to mulityply with.
I finally found one solution to get the correct sum of a row-by-row multiplicaiton, however for that I did it in two measures. One doing the calculation, in the second using Sumx to get the correct sum.
You may try this:
Net Volume = MAXX ( RELATEDTABLE ( dtPrice ), dtPrice[Retail Price] ) * ( MAXX ( RELATEDTABLE ( dtDiscount ), dtDiscount[Discount] ) ) * MAXX ( dtQty, dtQty[Qty] )Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedInWrapping the expression in Sumx made it work. So now I have the calculations in one expression and a single measure.
For reference for other users, if you need to multiply columns with each other and want to do it using a measure, below code can be used. It is multiplying price with a discount factor and a quantity, to get to a net volume. The measure is created in the PriceTable.
NetVolume = Sumx (PriceTable; MAXX( RELATEDTABLE ( PriceTable ); PriceTable[Retail Price] )* ( MAXX ( RELATEDTABLE ( DiscountTable ); DiscountTable[Discount factor] ) )* ( MAXX ( RELATEDTABLE ( QtyTable ); QtyTable[Qty] ) ) )
12 Replies
- Greg_Deckler
Community Champion
So, source data always helps, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
- [row column} By [row column] in same table
If calculated column, [Column 1] * [Column 2]
- [row column] by [row column] in different tables
If calculated column in table 1, [Column 1] * MAXX(RELATEDTABLE('Table 2',[Column])
- [row column] by [measure] in same table
If calculated column, [Column 1] * [Measure]
- [row column] by [measure] in different tables
If calculated column, [Column 1] * [Measure]- [measure] by [measure] in same table
If calculated column, [Measure 1] * [Measure 2]
- [measure] by [measure] in different tables
If calculated column, [Measure 1] * [Measure 2]
1upBelow please find a mockup table with the expected result, following line multiplication, as well as a simple relation model.
The Volume measure is calculated using above formula, and seperately afterwards using Sumx-function.
If there is a decision between using a calculated column or measure, I'd prefer the measure due to its flexibility (as far as I know now).
The aim of the calculation is to from a retail price, multiplying with a discount factor, getting a net price, then multiplying again with corresponding quantity, to get to a net volume, for each part number.
- vivran22
Hello 1up ,
You may create a calculated column in your quantity table:
Net Price = MAXX ( RELATEDTABLE ( dtPrice ), dtPrice[Retail Price] ) * ( MAXX ( RELATEDTABLE ( dtDiscount ), dtDiscount[Discount] ) ) * dtQty[Qty]Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedIn
