topic Re: Multiply columns row by row from two different tables and get correct sum in DAX Commands and Tips

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

1up — Fri, 28 Feb 2020 10:25:43 GMT

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

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

Greg_Deckler — Fri, 28 Feb 2020 10:37:15 GMT

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]

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

1up — Fri, 28 Feb 2020 12:06:07 GMT

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.

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

vivran22 — Fri, 28 Feb 2020 12:45:47 GMT

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

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

1up — Fri, 28 Feb 2020 13:43:57 GMT

Thanks a lot Vivran.

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

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

vivran22 — Fri, 28 Feb 2020 14:03:28 GMT

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

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

Anonymous — Sat, 29 Feb 2020 12:16:56 GMT

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

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

1up — Mon, 02 Mar 2020 07:45:38 GMT

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

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

1up — Mon, 02 Mar 2020 08:41:24 GMT

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

Any idea what could be amiss?

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

1up — Mon, 02 Mar 2020 09:01:15 GMT

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

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

Anonymous — Mon, 02 Mar 2020 09:16:18 GMT

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

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

1up — Mon, 02 Mar 2020 09:33:45 GMT

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,

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

Mohapatra-Akank — Sat, 07 Jan 2023 21:48:35 GMT

You could simply use value function -

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

and it works just fine!