Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

PaulDBrown

Creating a “custom” or “hybrid” matrix in PowerBI

Backround

It seems that the request for help in creating a “custom” or “hybrid” matrix is fairly frequent in the PowerBI forums (I have come across three different threads asking for assistance regarding this topic over the last week alone).

While the default table and matrix visuals do not cater for these types of custom layouts of information, developing a solution is not actually that complicated, though it does require some work.
As is normally the case with PowerBI, there are probably a number of ways to achieve this, but I thought I would showcase a method I find relatively “painless”.

The challenge: How can we create a “custom” layout for the information in a matrix?

In the following example we wish to include a combination of columns of aggregated values as well as values filtered by a dimension itself.

Let me try to explain what I mean by the question above. Let’s say we have a simple model for sales by Channel and Item and we would like to see the performance of the sales against a target over selected periods. The default matrix visual will allow us to visualise this, albeit the layout of the information is restricted to the default structure inherent to the visual. For example we can show the information:

1) Having the dimensions and period itself as rows, and the [Sum of Actuals], Sum of Target]  and [% over target] as values:

Default rows.JPG

 

2) Having the Dimensions as rows, the periods as columns, and the [Sum of Actuals], [Sum of Target] and [% over target] as values:

default columns.JPG

 

However there is no option currently to “turn off” certain elements in the headers (or rearrange the actual structure to a specific custom layout). If we want to see the periods as columns, each value (in this case three different measures) is shown for each period.

What if we wanted to “customise” the structure of the visual to show something like this? We can actually create a custom column layout  to show the information we need. We just need to define it.

Hybrid.JPG

 

One way to create a “Custom” or “Hybrid” matrix:

First the model for the example: Two fact tables (Actuals1 & Target2) and three dimension tables (Calendar Table, Dim Item and Dim Channel)

the model.JPG

 

Step 1: Creating the custom layout for the columns of the matrix

The key to this solution is to create a “custom” column layout to use in the column bucket in the matrix. In this example, the column layout we are seeking is this:

Hybrid Columns.JPG

 

and the way to replicate this structure is by creating a corresponding table where each column header is a row of a table. This should not be hard to do in Power Query/Dax. For the purpose of this exercise

  • Create a table with three rows for the aggregate values (and an index for sorting/filtering purposes):

Values header table.JPG

  • Create a period table using the Calendar Table [Period] column referenced to the Calendar table already present in the model (in this case, the index starts at “4” to cater for the previous table rows in the previous table created):

period table.JPG

 

  • …and by appending these two tables, we have created the structure we need for the custom layout (let’s call this table “Hybrid Table”):

Hybrid table.JPG

 

Since the periods in this table are referenced to the calendar table, the rows will “grow” accordingly.

Something worth mentioning at this point is that the index column is important for two reasons:

  1. It allows for the correct sorting of the actual columns, even if the periods are filtered
  2. The index column is actually very useful in the FILTER expressions of the measures needed.

This table is unrelated to/disconnected from any other table in the model.

Full model.JPG

 

 

 

Just beware that if we are tempted to check the matrix out with the new column headers we have created the visual will “refuse to render” and complain about the lack of relationships between tables… just ignore this for now.

visual error.JPG

 

Step 2: Creating the measures for the custom matrix

Let the DAX fun begin…!

Filter Context.JPG

 

Since we are using as columns for our matrix an unrelated table, there are two main issues to be aware of regarding the filter context (with regards to the columns in the matrix) of the measures we are going to use. For this example:

  1. Which measures can rely on the actual relationships established in the model (the “aggregated values)

We can use the simple aggregation measures for these values (simple SUM and DIVIDE functions). We will specify the filter context for these in the final measure.

SumActuals.JPG

  

SumTarget.JPG

 

%DiffTarget.JPG

 

           2. In which measures we need to be specific as to the filter context and write the appropriate DAX to relate the filter to the corresponding context in the model.  (the “Period Values”)

For these measures we need to be specific about the filter context we want in the calculation. Since we are using columns from the “Hybrid Table” (which has no relationships in the model), we need to use a function to establish a virtual “one-to-one/many” relationship (TREATAS function) to enforce the filter context and filter the values accordingly using the expression as the filter in the measure.

TREATAS expression.JPG

 

Basically what we are doing is saying: “when the matrix column = Jan – 2020, filter the rows to show those which are filtered by the Calendar Table [Period] = Jan – 2020”.

 

In this example, we want to show the % difference between the actuals vs the target for each period in the matrix:

TREATAS.JPG

 

Having established the measures we need, all we need now is the final measure we will use in the matrix to take into account the filter context for each of the “HybridColumns” we are using in the matrix columns. This measure will establish which of the previous measures must be used based on the filter context established by the columns included in the matrix from our Hybrid Table. Since the actual results vary from whole/decimal numbers, we also need to specify the FORMAT required for each result:
The filter context is established by the columns:
Hybrid Columns.JPG

 Which come from our “Hybrid Table” (we are using the index reference for the Hybrid table for simplicity purposes):

Hybrid table.JPG

 

And this is the final measure which we will use for the values in the matrix:

Final Measure.JPG

 We can now create the matrix using:
         a) The Channel and Item dimensions as rows.

         b) The HybridColumns from our Hybrid Table for the Column Bucket

         c) The final measure [Values for Matrix] in the values bucket to get this:

         d) (some conditional formatting for the values displayed)

Final Custom Matrix.jpg

 

Step 3: Enabling the filtering of the columns in the custom matrix


Since we want to be able to filter the matrix by our periods of choice in a slicer, we need one more measure. The slicer on the page is from the ‘Calendar Table’ [Period] column, so we need to be able to select the periods to be shown in the matrix based on the selection made on this slicer from the calendar table. The measure must compare the values selected in the slicer with those established in the visual from the Hybrid Table:

FILTER Matrix1.JPG

 

We can then use this filter in the filter pane for the matrix visual to filter the columns based on the selection in the Calendar Table [Period] Slicer. We set the TopN for the HybridColumns field in the filter Pane  as “1” to make the visual respond to the selection made in the ‘Calendar Table’ [Period] Slicer.

TopN filter.JPG

 

We thereby can now use the Calendar Slicer (Calendar Table [Period]) to filter the periods we want to see in the visual.

 

Conclusion: The flexibility offered by PowerBI and DAX allows us to create “custom” matrices to reflect the layout of information we need to depict in our reports.

 

PS: If this tutorial is unclear, please feel free to contact me on this thread or via pm!

Comments

Could you please share PBIX working file for this example

@abubakrsaad 

 

Sure, here is the link to the file:

Hybrid Matrix PBIX 

thanks a lot for your prompt and valuable reply

Thanks alot and earn valuable trick from this.

@PaulDBrown - this is a great article!  Thank you for sharing! 

 

Any chance you would have any insight into how to make the custom matrix performance better?  I am going to have 100+ columns (I realize that the matrix can only display 100 columns at a time) and my DAX measure is looking at 204 combinations / selectedvalues.  I'm assuming the DAX measure is the reason why my performance in the matrix is not good.  Any advice?  Thanks!

@wjkolesar 

I would imagine the culprit is probably the  TREATAS function (+ the fact that you will be needing 200+ combinations)

Having said that, that is a wopping matrix you are going to show. Have you tried creating a "regular" matrix with the columns / measures to compare the performance?

You might also try running the measure through DAX Studio to see where the highest impact on perform is taking place

@PaulDBrown thanks for the response!  The "regular" matrix performamce works great - instant results as I drill up / down on my dimensions and make slicer seletions, etc.

 

The problem with the "regular" matrix is that I have measures that tie to a specific Month Year (so I have Month Year as my column) and then 5 measures that are an "overall" that do not tie to a Month Year.  Using your post, I was able to create a matrix with each column being a Month Year + Measure name and then sorted it correctly so that the most current Month Year + Measure  name displays first and the overall measures (without Month Year) are always at the very end.  As time goes on, each month will add 6 additional columns (aka Month Year + Measure name).

 

wjkolesar_0-1607018557901.png

Thanks for DAX Studio note - I will defintely try that and see where my performance issue is occuring.  Thanks!

@wjkolesar 

Just curious...Did you manage to improve the performance?

I've been giving this some thought and there may be a way to improve performance, though it may only be slightly.

You can actually avoid using TREATAS and use USERELATIONSHIP instead (which reportedly offers a better performance than TREATAS).

You need to set up the model (following my example) linking the hybrid matrix table to the Calendar table (in this case via the 'Calendar'[period]  field. This relationship must remain inactive

Userelationship model.JPG

 

Then, instead of the TREATAS measure, you use:

 

% diff by period USERELATIONSHIP = 
CALCULATE(
    DIVIDE([Sum of Actuals], [Sum of Target]) - 1, 
   USERELATIONSHIP('Hybrid Table'[HybridColumns], 'Calendar Table'[Period])
   )

 

You then use this measure in the final measure (instead of the TREATAS measure) used in the visual, and all the rest remains the same.

The final measure to use in the visual:

 

Values for Matrix USRELATIONSHIP = 
VAR calc = IF(SELECTEDVALUE('Hybrid Table'[Index]) = 1, [Sum of Actuals],
            IF(SELECTEDVALUE('Hybrid Table'[Index]) = 2, [Sum of Target],
                IF(SELECTEDVALUE('Hybrid Table'[Index]) = 3, FORMAT([% diff over Target], "#0.0%"), 
                        FORMAT([% diff by period USERELATIONSHIP], "#0.0%"))))
Return
IF(AND(ISBLANK([Sum of Actuals]), ISBLANK([Sum of Target])), BLANK(), calc) // The AND expression is to exclude unnnecessary blank values in the final matrix visual

 

 

Hope this helps!

 

PS: the link to this new file using USERELATIONSHIP:

Hybrid matrix using USERELATIONSHIP instead of TREATAS 

Hi Paul. Just one question. Once you export the table to an Excel file, you got the same format of the matrix??

@carlos_pinav 

I´m afraid not: when you export any visual to Excel, the only option is as a csv file (Desktop) and csv/Excel Table from the service. So you would need to set up the matrix thereon.

Anonymous

As great as PBI is... it's a real shame that such a seemingly simple thing requires so much work on the part of the so-often-inexperienced modeler. No wonder that many people coming from other backgrounds, like QlikView or Tableau, will immediately cringe and throw PBI to a rubbish bin when they see stunts like this. I would most likely do the same, even though today I can do anything with DAX and PBI... REAL SHAME 'cause simple things should be simple.

Hi @PaulDBrown ,

 

I'm looking to do something similiar as what you demonstrated in this article but I was wondering if the functionality to drill-down into the data for a particular matrix cell is still possible with this hypbrid matrix approach? 

@bjelen 

I believe the drill down features work with these type of matrices, and you can also include report page tooltips. Iv'e included the link to the sample PBIX file for you to play with.

Drill down.gif

@PaulDBrown  This is great Explanation and helps alot in creating custom columns. My problem has slight addition here in my senario i have 7 different metrics and i have to align them in columns in increasing order based on thier ranks and based on selection of region(ideally region should not filtered data but records for that region should be highlighted-though that can be handled with conditional formatting), but here challenge is to align 7 different(few 4 Whole numbers and 3 are %) metrics in columns based on their rank for a selected (filtered) dimension(region) and best rank measure for a selected region should be first then second..and so forth. so how would I align the order basedon thier rank. Am struggling here a bit!! 

Thanks

@Amit_Saxena 

If I'm understanding your question correctly, I don't believe you can order columns dynamically in PowerBI.