Multiply columns row by row from two different tables and get correct sum

[1up]

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.

Volume = (SELECTEDVALUE(Price[Retail Price]) * SELECTEDVALUE(Discount[Discount factor])) * SELECTEDVALUE(Qty[Qty])

[vivran22 · Accepted solution]

@1up 

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 LinkedIn

[Greg_Deckler]

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]

[1up]

Below 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.

[Anonymous]

This stuff should be done in Power Query. DAX is not a language for this. No wonder you guys struggle with things. If you use the right tool for the right job, you'd be saving yourself a lot of time and frustration.

The very fact that something is doable in DAX does not mean it should be done in DAX. Easy as that.

Best
D

[1up]

---

Could you please explain a bit more, D? I am under the impression using the Query editor will create calculated columns. That the measure-option is out of scope in the query editor? If it is easier to do this in the Query editor , then I am all ears. 🙂

@Anonymous wrote:
This stuff should be done in Power Query. DAX is not a language for this. No wonder you guys struggle with things. If you use the right tool for the right job, you'd be saving yourself a lot of time and frustration.

The very fact that something is doable in DAX does not mean it should be done in DAX. Easy as that.

Best
D

---

[Anonymous]

And I was under the impression that you needed a calculated column. At least in some of the cases you listed. It stands to reason of course that when a measure is involved, you can't use PQ. But when you need to perform an operation in a model table row by row, then PQ is the right way, not DAX.

Best
D

[1up]

Good then we are on the same page. I tried to go all in with getting solutions for all possible combinations, as the code for measures seem to vary depending on if columns and / or measures are used, and I seem to not get them to work, until now after some good help on the way.

Cheers,

[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

[1up]

Thanks a lot Vivran.

Is there an expression which will create the same result using a measure?

[vivran22 · Accepted solution]

@1up 

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 LinkedIn

[Mohapatra-Akank]

You could simply use value function -

VALUE(TABLE1[COL1]) * VALUE(TABLE2[COL2])

and it works just fine! 

[1up]

Hi, in my example the sum is incorrect unfortunately, 385 vs 463. The bottom row is the Total.

Any idea what could be amiss?

[1up · Accepted solution]

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