Forum Discussion

powerbeei's avatar
powerbeei
Regular Visitor
3 years ago
Solved

Matrix aggregate match

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,

 

 

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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. 

     

4 Replies

  • powerbeei's avatar
    powerbeei
    Regular Visitor
    3 years ago

    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's avatar
      FreemanZ
      Super User
      3 years ago

      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's avatar
        powerbeei
        Regular Visitor
        3 years ago

        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.