cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
4c5da7 Frequent Visitor
Frequent Visitor

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

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, 

 

 

123.PNG

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

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

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)