Matrix aggregate match

[powerbeei]

Hi All,

Can anyone please help me with the below ? My Data looks similar to the below two tables.

I want to create a matrix based on these tables.

                 

Result :

 Basically, I want to put the old amount and the new amount  against the old category in the matrix.

Any help is appreciated.

Thanks,

[powerbeei]

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

[FreemanZ · Accepted solution]

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. 

[powerbeei]

Thanks @FreemanZ . Think will have to use Categories to relate the table as you suggested. Was hoping to use dates if I need to plot YTD etc. but will come with work around if needed.

Thanks for your help.

[VahidDM]

Would you please send your sample data table in a text format to be able to copy and paste them into PBI?

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube