Forum Discussion
Getting the column sum when multiplying with another table.
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!
3 Replies
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:
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.
