Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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.
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.
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |