Getting the column sum when multiplying with another table.

[JohannesM]

Hi,

I have two tables and I'm using DAX to multiply a column from table1 with a column from table2. The two tables are connected by a key so it is multiplying per row (the key is built on Group and Year).
It all looks good if I include both columns in the visualisation but if I remove the column from table2 then the sum I get is as if I muliplied with the total from table2, not row by row. My data is sensitive but it looks like this:

table1                                             table2

Group	Year	Cost			Group	Year	Prod
A	2020	1			A	2020	16
A	2021	3			B	2020	15
A	2022	5			C	2020	18
B	2020	3			A	2021	3
B	2021	6			B	2021	1
B	2022	8			C	2021	18
C	2020	5			A	2022	18
C	2021	6			B	2022	19
C	2022	7			C	2022	3

Measure1 =

VAR cost = SUM('table1[Cost])

VAR prod = SUM('table2[Prod])

RETURN

cost*prod

I guess I need to tell DAX to sum Prod row by row and I have tried but with no success. Suggestions anyone?

Thanks in advance!

[FreemanZ]

hi @JohannesM 

it needs both columns to find the unique match.

Actually you can use Merge Query in Power Query, by bring the Table1[Cost] column to Table2. Ensure to select both the group and year in merging. Check this:

https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616...

[JohannesM]

Actually the problem I have is that not both columns can have an unique match since it's one-to-many. But I'm sure that the multiplying is possible. It is in the column with unique match that I want to get the row instead of column when multiplying.

[JohannesM]

Hi @FreemanZ 
But isn't that what I already do with the relation in PBIs "Model"-page?  RowID in the picture below is based on Group and Year to get that unique match.