Matrix Visual, Display Columns under a Column based on year.

[4c5da7]

Hi guys, I was wondering if this is possible in power bi matrix visual, 

or if there's a customized visual for this scenario it will be a big help, 

I was looking for a way to achieve this kind of visual in the desktop version 

which shows the actuals per year

 the columns under are the present in my database, 

I have no problem relating required tables to one another. and creating calculations for the other columns, 

my only problem is that when I try to use the standard matrix visual. 

take note : I'm Currently using 3 Tables for this idea.

 as follows : 

  1. Table for Item Actual amount(s) | Quantity (*Item No., *date)

  2. Table for Item Budget amount(s) | Quantity (*Item No., *date) 
  3. Calculated Table for the Year.  (*date) |which is filtered by year. 

[v-lili6-msft · Accepted solution]

hi @4c5da7 

There are two way for you to achieve your requirement:

1. create a dim Item table and create relationship with Table for Item Actual amount(s) and Table for Item Budget amount(s).

and also use Calculated Table for the Year to create relationship with Table for Item Actual amount(s) and Table for Item Budget amount(s) too.

Then use Year field from Calculated Table for the Year in [Columns] and Item from Dim Item table in [Rows] in matrix. Now darg data from these two fact table in [Value]

2. add a flag column "Actual" in Table for Item Actual amount(s) and add a flag column "Budget" in Table for Item Budget amount(s),

and use Apppend in edit queries or UNION Function to append them into one table and then create the relationship with Calculated Table for the Year.

Now create the visual that Year field from Calculated Table for the Year in [Columns] and also flag from new table in [Columns], Item from new table in [Rows], now drag data from the fact table in [Value].

Regards,

Lin