Improve the Readability of the Matrix Visual.

It’s no surprise that a program aimed at the pivot table loving business user would include a similar visual called the Matrix. The ability to summarize, search, and drill into transaction detail makes it a fast friend of any Accountant or Financial Analyst. However, ask your report users to navigate this visual and you may quickly discover that many users are unsure how to fully take advantage of the details under the surface of the initial visual itself.

By applying some simple and creative ideas, we were able to provide a user friendly matrix that brought information to the surface quickly, and helped tell a clear data story.

Let’s get to it!

1. Roll up relevant detail into one single row level:

Summary:

We found that by asking a reader to drill into multiple row levels regardless of the statement line type, it only took about three clicks for the report to lose its familiar shape. Users would see a hierarchy of sometimes blank rows taking up valuable reporting space and rarely find what they were looking for. We tried to show them the different drill in options to clean the report view, to no avail. They needed easy access, and were not interested in earning a black belt in the drill-down feature. This solution uses DAX in a calculated column to pick out and combine relevant detail specific to the statement line type. As a result, we only need one drill down row level to find what we need, no more, and no less.

Details:

In our early matrix visuals, we’d stack all the informational table fields in the row fields of the matrix (Figure 1). This format would fully expand to the visual you see in Figure 2. In just a few clicks, we see how a nice-looking summary visual can quickly stretch out and turn into excess rows, taking up valuable space in the report.

Figure 2 below illustrates this when looking at our Gross Sales line. All we really need here is just to show sales grouped by the customer name. What we get instead is every statement field, including blank fields that happen to be relevant to show for expense lines, such as document number, vendor name, and an explanation field.

Now let’s use some DAX (Figure 3) in a calculated column (Or for better performance, use a custom column in Power Query) to help us define that ideal single line of information that will help us pick and choose what we display on that first drill down row. We will call this new column Line2 to represent the second row level in our matrix.

We’ll first use variable to ignore blank data fields, then assign a helpful data label that leads and distinguish say a document number from a vendor number for clarity (i.e. DOC# is our document number; VEN# is our vendor number etc.). Next, we identify when our DAX is in a Sales vs. Expense row in our financial statement. By doing so, we will call the SALES variable in Sales, and EXPENSE variable in Expense lines. Finally, our variable “MATH” will bring it together into the result.

With the Sales result in Figure 4, we see only one level drilled down, and Sales is properly grouped only by Customer name. In Figure 5 we see our first level Expense lines now includes the other required detail expense items such as Doc# and Vendor# and a Note field.

As an alternative, we can skip the need for the user to drill down entirely by by providing a side table to the right that cross filters the main financial statement when a specific line is selected.

2. Concatenate information into the top row level with CONTATENATEX:

Summary:

By using the DAX function CONTATENATEX, we are able to bring a few high-level grouping labels up to the top row of the matrix visual, as a preview of the different row labels that lie beneath.

Details:

Now that we’ve seen how to bring important information to the second-row level, let’s see if we can bring a small preview of key information up to the first-row level of the matrix.

By using the DAX function CONCATENATEX, we can roll up text that would normally display in multiple rows and combine them all into one cell at on the top-level row of the matrix.

In the DAX measure below we create the line check (LINECHK) variable to create a simple flag (0 or 1) to identify when we are in a detail line vs. a total row. Next we use the variable COMBINE to iterate the Supplier column by using a comma as our deliminator. Finally, we add a condition to our final MATH result that simply checks our row flag (LINECHK) and passes through the result from the variable COMBINE when necessary.

In our result highlighted in Figure 8, we see the fully expanded table with our new Vendor measure that mirrors the vendor line in the rows section of the matrix. When rolled up, the vendor name that had appeared on each row, are now combined and displayed in a single top level cell, giving us a preview of what lies beneath our table.

3. Use Power Query transformations to create dynamic row labels (no drilling required!):

Summary:

By replacing the matrix visual with a table visual, and creating a new dimension table by referencing the fact table in Power Query, we are able to replace the functionality of the drill feature ‘Expand new level’. This gives the user a familiar slicer to dynamically change the table rows labels in the report.

Details:

This is a tip that I learned from Robin Abramson (Pragmatic Works) at one of our Portland Oregon user groups. It’s proven to be very useful in our business as an alternative to the matrix visual for users.

In this example, we essentially create a new dimension table that will allow us to slice our Sales Fact table by three different attributes currently displayed on different columns of our Sales Fact table: State sold in, Customer name, and Brand purchased.

Note: The key to getting this new dimension table to work correctly along with the other dimension tables, is to ensure that your original Sales Fact table has a unique ID for each row of the table. If you have an order with three lines of sales, you will need a column that combines order and order line number. Each order line needs to be unique.

We first use Power Query to make a reference of our original Sales Fact table. In the Power Query ‘Queries pane’ (Figure 9) Right-click over the Sales Fact table and choose ‘Reference’. This new table (Named Sales Fact (2)) avoids another import of your Sales Fact table, and is based on the last transformation made in the original table Sales Fact (2).

In our new Sales Fact (2) reference table, we need only two table elements to remain, the unique key field (in our case the unique Order field), and the category fields (Customer, State, and Brand) we ultimately want to slice by.

Remove the Amount field, as we will only need the amount column in the original Sales Fact table.

Once we remove sales and are left with only the necessary columns shown below (Figure 10), we are now ready to transform this in Power Query to create our new attribute dimension table.

Highlight the Order column and select Transform and Unpivot Other Columns as shown below in Figure 11.

By Un-Pivoting Other Columns, we end up reducing three columns into two, Attributes and Values. The Attribute will be column source for the slicer items we want the user to be able to choose from. The Value column will contain the result of the slicer and be used on the rows of our table. The Order column will now display duplicate Order/line numbers repeated for each row that lists the different Attributes in the same order. There is no concern here as when we place a slicer filter on this as a dimension table, we will reduce our table down to a unique Attribute and set of Order numbers, creating a one to one match.

We load this table and create a relationship on the Order field. When doing so, we will be creating a one to many relationship. As you can see with the arrow direction below, our cross-filter direction uses our original fact table to filter our new dimension.

If this is the case, we want to change the Cardinality to Many to many, then switch the Cross filter direction so that Sales Fact (2) filters Sales Fact. We should end up with the filter direction arrow pointing towards the Sales Fact table. Open the relationship between Sales Fact and our dimension Sales Fact (2), and select the Many to Many option as we do in Figure 14. Then adjust the Cross Filter direction so that Sales Fact (2) filters Sales Fact.

At this point, our Sales Fact (2) table will serve as another dimension table. We will use the Attribute column from Sales Fact (2) in our slicer, and place the Values column in our table in place of the row labels.

Once we slice an attribute, the rows in the table will ensure we are only filtering the desired row labels from the Value column.

When we slice by the Attribute column, we really are only filtering the fact table for amounts that relate to the selected Order number. We leave the grouping up to the Attribute field we choose from our new dimension. The Value field determines which of the Sales Fact dollars we group and summarize. From this point, you can filter the other dimensions just as you normally would to further narrow in on the information you are looking for.

For example if I am interested in how much of the brand Parker we sold to Mark, Click on Brand attribute, then on Customer Mark to reveal that he purchased $100 worth of product (Click the link below to try the report).

Hopefully this is helpful in adding some new functionality and clarity to your Matrix. I would love to hear your comments and what works for you using the matrix visual in practice. Have a great week!