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 : ...
  • 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. 

     

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.