topic Re: Multiply values in columns (Different tables) in DAX Commands and Tips

Multiply values in columns (Different tables)

Anonymous — Thu, 19 May 2022 10:26:09 GMT

Say I have one table like this:

a	1,02
b	-0,4
c	-5
a	-3,2
b	3
c	-4,5

And the other table like this:

a	839403284
b	932848032
c	39842048

How to I multiply each of the values in the first table with the corresponding values in the second table?

And what should the relation on the "Model"-page look like?

Thanks!

Re: Multiply values in columns (Different tables)

johnt75 — Thu, 19 May 2022 11:12:18 GMT

If you know that there will only be 1 entry per item in the 2nd table then you can create a one-to-many relationship between the tables. You would then be able to use the RELATED function to do the multiplication. e.g. you could create a column on the first table like

Multiplied value = 'Table 1'[Value] * RELATED('Table 2'[Value])

Re: Multiply values in columns (Different tables)

Anonymous — Thu, 19 May 2022 11:18:05 GMT

It won't let me make that formula.. 😕

Re: Multiply values in columns (Different tables)

AnamPatel — Thu, 19 May 2022 11:20:34 GMT

Firstly you can create a relationship between the two tables based on the common column. Now you can use the below DAX to do the multiplication -

Multiply = TABLE1[COLUMN1]* RELATED(TABLE2[COLUMN2])

Model Should look something like this, just a sample image -

Let me know if this helped you.

Re: Multiply values in columns (Different tables)

AnamPatel — Thu, 19 May 2022 11:19:28 GMT

Firstly you can create a relationship between the two tables based on the common column. Now you can use the below DAX to do the multiplication -

Multilple = TABLE1[COLUMN1]* RELATED(TABLE2[COLUMN2])

Model Should look something like this, just a sample image -

Let me know this helped you.

Re: Multiply values in columns (Different tables)

johnt75 — Thu, 19 May 2022 11:22:24 GMT

can you share a picture of the diagram view showing the relationship between the tables ?

Re: Multiply values in columns (Different tables)

Anonymous — Thu, 19 May 2022 11:22:34 GMT

It won't let me chose the right columns.. 😞
What should I do?

Re: Multiply values in columns (Different tables)

Anonymous — Thu, 19 May 2022 11:25:21 GMT

This works for most categories:

But for certain others it's completely wrong... why would that be?

Re: Multiply values in columns (Different tables)

Anonymous — Thu, 19 May 2022 11:28:40 GMT

It's a simple, many-to-one relationship.
One table has only one occurence of each value, while the other table has lots of occurences of each value. Does it matter wheter it's formatted as numbers or text? (the values that are related are 4-number values)

Re: Multiply values in columns (Different tables)

johnt75 — Thu, 19 May 2022 11:33:32 GMT

It doesn't matter whether they are numbers or text but they both need to be the same. and if its text you need to make sure that there are no leading or trailing spaces in either column, as that would throw the relationship out

Re: Multiply values in columns (Different tables)

AnamPatel — Thu, 19 May 2022 11:37:12 GMT

Create a relationship between the two tables first.

Re: Multiply values in columns (Different tables)

Anonymous — Thu, 19 May 2022 11:56:43 GMT

All of that should be in order.

Re: Multiply values in columns (Different tables)

Anonymous — Thu, 19 May 2022 11:57:31 GMT

That should be in order, but there are still some of the calculations that are completely wrong..

Re: Multiply values in columns (Different tables)

tamerj1 — Thu, 19 May 2022 12:16:10 GMT

Hi Anonymous
My Undestanding that you cannot create calculated columns and you cannot create or edit relationships. Then try to create New Measure

NewMeasure = SUMX ( Table1, VAR Name = Table1[Name] VAR Value1 = Table1[Value] VAR Value2 = MAXX ( FILTER ( Table2, Table2[Name] = Name ), Table2[Value] ) RETURN Value1 * Value2 )

Re: Multiply values in columns (Different tables)

Anonymous — Wed, 25 May 2022 06:04:09 GMT

Hi Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure = MAX('Table'[value])*MAX('Table (2)'[val])

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Multiply values in columns (Different tables)

sam888 — Thu, 16 Feb 2023 01:58:49 GMT

unrelated, but this solved a problem I was stuck in for days. thank you.

Re: Multiply values in columns (Different tables)

BobSled — Wed, 24 May 2023 20:10:35 GMT

Hello, in the idea you provided, what is the "name"? i understand what table and values are but i dont understand whats supposed to go where is says name

Re: Multiply values in columns (Different tables)

tamerj1 — Thu, 25 May 2023 03:01:33 GMT

BobSled

The name of the first column that contains "a", "b", "c"

Re: Multiply values in columns (Different tables)

BobSled — Fri, 26 May 2023 20:43:45 GMT

This worked perfectly for me thank you very much!