Easiest way to multiply values from columns in different tables?

[eeew00]

Hello and greetings from a total amateur, spare time BI:ier,

My scenario:

I have numeric values in Col1a which is in in Table1 and Col1b which is in table2. I would like to multiply them with each other on a row-base (So that I can see the result on each row in matrix visual and a grand total)

At first I tried SUMX * SUMX which gave a nice result on each row, except for the grand total which was horribly wrong.

Later I tried SUMX(col1a * col1b) but that only seems to work when the columns are on the same table..

There's a billion posts online on how to do this properly, but I can't for the life of me understand the answers with 4-5 nested DAX functions.. 
I would like to do it via DAX and not PowerQuery. 

Any help would be greatly appreciated.

BR,

Eelis

[Anonymous · Accepted solution]

@eeew00 
The logic of total of Sumx()*Sumx() is sum(col1) multiply sum(col2), it does not sum the multiply product.

I would suggest you to create a calculate column. Check my example:

I don't know how is your data looks like but I guess the two tables are related with each others by two columns. If not, you can also add an index column to each table, then create a 1:1 relationship. 

Column = 'Table'[Value]*RELATED('Table (2)'[Value])

pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EbhhtLGxfD9BuwGOxHGW...

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[eeew00]

Thank you! That did the trick 🙂

[Pragati11]

HI @eeew00 ,

Did you try the solution mentioned on the following existing threads?

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Multiply-columns-row-by-row-from-two-differen...

https://community.powerbi.com/t5/Desktop/Multiply-2-columns-from-2-different-tables/m-p/722762

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[eeew00]

Thanks! Unfortunately the example with SELECTEDVALUE leaves the totals field blank, and the other example with SUMX * SUMX gives an incorrect total

[Pragati11]

Hi @eeew00 ,

In that case I will some sample data from both of your tables and some screenshot of the output.

If you can share pbix file that will be better.

Thanks,

Pragati