How toCreate a calculated column by multiplying two columns where one column is in a different table

Question

How to Create a calculated column by multiplying two columns where one column is in a different table?&nbsp;Tables are connected with M2M cardinality so RELATED function dax is not working&nbsp;Kindly help

anonymous · Accepted Answer

Hi mukherjeerahul&nbsp;,
&nbsp;
Thanks for the reply from Wilson_&nbsp;.
&nbsp;
Here is the sample data I created:
&nbsp;
TableA:

ProductID

Value

1

2

1

3

1

4

1

2

1

3

1

4

1

2

1

3

1

4

1

2

1

3

1

4

1

2

1

3

1

4

1

&nbsp;
Table B:

ProductID

Value

1

2

3

5

4

7

1

9

2

3

4

6

1

8

2

10

3

1

4

3

1

5

2

7

3

9

4

2

1

4

2

6

3

8

4

10

&nbsp;
This is the model diagram of my two tables:

&nbsp;
Please try:
&nbsp;
Create an intermediate table to aggregate values from TableA and TableB by ProductID, the syntax is as follows:
Table =
SUMMARIZE(
     TableA,
     TableA[ProductID],
     "SumValueA", SUMX(FILTER(TableA, TableA[ProductID] = EARLIER(TableA[ProductID])), TableA[Value]),
     "SumValueB", SUMX(FILTER(TableB, TableB[ProductID] = EARLIER(TableA[ProductID])), TableB[Value])
)
&nbsp;
Create a new column to calculate the product of 'Table'[SumValueA] and 'Table'[SumValueB]
Column = 'Table'[SumValueA] * 'Table'[SumValueB]
&nbsp;
The page effect is as follows:

&nbsp;
If you have any further questions please feel free to contact me.
&nbsp;
pbix file is attached.
&nbsp;
Best Regards,YangCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly.If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!

Forum Discussion

Related content

Calculating with different multipliers based on value

Dynamic Calculation Different Period

Different Calculations

Multiply values in columns (Different tables)

1. How to add two different calculated columns of two different table