Forum Discussion
Matrix aggregate match
hi powerbeei
try to
1) relate two tables via Cat and Cat_new column
2) write three measures like:
_Amt1 = SUM(Table1[Amt1]) _Amt2 = SUM(Table2[Amt2]) Variance = [_Amt1] - [_Amt2]3) plot a matrix in Power BI Desktop or pivot table in Excel like:
The most critical part is to relate two tables via Cat and Cat_new column, instead by Key columns suggested by default.
Hi VahidDM Sorry, Please see the data below
Key Column1 Cat Amt1
1000|21/02/2022 A E1 252
1000|21/02/2023 A E2 255
1001|21/02/2024 A E3 258
1002|21/02/2025 A E4 261
1000|21/02/2026 A E5 264
1000|21/02/2027 A E6 267
1000|21/02/2030 B F0 276
1003|21/02/2031 B F1 279
1000|21/02/2029 C H0 273
1000|21/02/2028 C H1 270
Table 2
Key Cat_new Amt2
1001|21/02/2024 E1 125
1000|21/02/2023 E2 101
1000|21/02/2022 E3 100
1002|21/02/2025 E3 125
1000|21/02/2026 E5 249
1000|21/02/2030 F0 125
1000|21/02/2027 F1 300
1003|21/02/2031 F1 158
1000|21/02/2028 H1 355
1000|21/02/2029 H1 387
hi powerbeei
try to
1) relate two tables via Cat and Cat_new column
2) write three measures like:
_Amt1 = SUM(Table1[Amt1])
_Amt2 = SUM(Table2[Amt2])
Variance = [_Amt1] - [_Amt2]
3) plot a matrix in Power BI Desktop or pivot table in Excel like:
The most critical part is to relate two tables via Cat and Cat_new column, instead by Key columns suggested by default.