Building a Matrix with Asymmetrical Columns and Rows in Power BI

I have written a few articles that are related to this topic (articles that utilise this same technique to solve a problem), but never before have I explained the stand alone concept of how to build a visual with asymmetrical columns and rows in Power BI.  Let me explain with the example below.

How a Visual Works by Default

This matrix in the image above is built using the Adventure Works database. Category (#1), Sub Category (#2) and Year (#3) are all columns coming from dimension tables in the model. The values section of this matrix (the numbers) come from the measure [Total Sales].  Power BI is designed to work this way. You use Columns to slice and dice your data, and measures to do the calculations.  The columns “filter” your data tables, then the measure is calculated on the data that remains after the filter is applied.  The example above is a matrix, but it is exactly the same for pie charts, column charts, tree maps, etc.

What If You Have a Need for an Asymmetric Visual?

Not every report you may want to build has such simple layout requirements as the one above.  When using an Excel pivot table, there is a feature called “Field, Items and Sets” that makes the process of creating asymmetric pivot tables relatively easy, but that doesn’t exist in Power BI.  To help you understand, here is an example of an asymmetric visual (Power BI matrix in this case).

Note in my example above, the first 4 years come from the year column, just like before, but now I have 2 additional columns at the end of the matrix that are not simply the addition of other columns.  To create the last 2 columns above I need to write measures, but you can’t have both measures and columns from your model showing like this in a Power BI matrix.  Instead you need to build a solution that leverages a separate table to generate the columns you need, and then to generate the results you need in each column.

Below I will show you how to build such an asymmetric matrix like this.

Why Not Just Use Measures?

This is a simplified version of the concept I am demonstrating here (a combination of columns and measures on a visual).  If you  needed a simple visual like the one above, it would of course be possible to write 6 measures and place them all in a single Matrix.  This will produce the visual above.  But there are many use-cases where that wont work.  What if you wanted to have a slicer like the one shown below instead of an asymmetric table?  You can’t do that with just measures.

Or what if you wanted a collapsible header/sub header like is shown on the rows of the matrix below, where the sales by year are additive and the variance numbers are grouped together?  You can’t do that with just measures either.

What I am demonstrating here is a simplified version of the header table technique that you can use when you can’t just use measures, for whatever reason.  If you can solve your problem by simply using measures, then of course you can do that without this technique.

An Overview of How to Build Such a Matrix

First let me show you a pseudo code table that conceptually explains what needs to be done to solve this problem.

Note that the first 4 rows (2001 – 2004) are just standard Power BI behaviours.  You can do this just by dragging the year column and the [Total Sales] measure.  But if you want the change in sales and % change in sale too, then you need to use a different approach than is standard.

There are currently 2 ways to do this.

1. SWITCH Measures with Header Tables (connected or disconnected)
2. Calculation Groups

I will cover both of these approaches in this article.

SWITCH Measures with Header Tables

You can use a Header Table and a SWITCH measure to obtain the required results in a Matrix.

Here are the steps:

• Create and load a Header table with the layout you want.
• Create a Relationship between the Header Table and the Calendar Table (if required).
• Write a SWITCH Measure to generate the result for each column item.
• Use the SWITCH Measure in your Report.

Let me take you through these steps.

Create a Header Table

I created the following Header Table and named it HeaderTable in Excel. It has the following columns:

• Display Value for the column headers of the Matrix
• Sort Order that I will use in the SWITCH measure and also to control the sort order of the display value.

Load the Header Table into Power BI

I loaded the HeaderTable from Excel workbook into my Power BI workbook.

Create a Relationship between the Header Table and Calendar Table

I created a relationship between the HeaderTable and Calendar Table using the Year column from the Calendar table and Display Value from the HeaderTable.

Now something to note here.  The first 4 values in the Display Value column will find a match in the Calendar table.  But the last 2 rows in that column will not match any record in the calendar table.  But that doesn’t matter because the SWITCH measure will leverage this relationship when it is useful (the first 4 rows) and it will override the relationship when it is not useful – more on that below.

Technically this model is now a Snowflake Schema and this is a good example of a time when it is OK to step away from the recommended Star Schema.

Write a SWITCH Measure to generate the result

The next step is to write the SWITCH measure that determines what result to display based on the displayed value.  Here is what I wrote.

 
Values to Display =
   VAR DisplayItem = SELECTEDVALUE ( HeaderTable[Sort Order] )
   VAR Sales2004 = CALCULATE([Total Sales],all(HeaderTable),HeaderTable[Display Value]="2004")
   VAR Sales2003 = CALCULATE([Total Sales],all(HeaderTable),HeaderTable[Display Value]="2003")
   VAR Chg = Sales2004 - Sales2003
   VAR ChgPct = DIVIDE(Chg,Sales2003)

   RETURN
     SWITCH (
        TRUE (),
        DisplayItem <= 4, [Total Sales],
        DisplayItem = 5, Chg,
        DisplayItem = 6, FORMAT(ChgPct,"#.0%")
     )

Note in the measure above that I am formatting the last display item (line 13) as a percentage using the FORMAT function.  This is actually a text format and not a numeric format.  The consequences of this are that you cannot use this technique in a chart of any kind, only visuals that simple display the numbers (or text in this case).

Use the SWITCH Measure in your Report

Next I built the matrix. I placed the column HeaderTable[Display Value] (#1 below) on Columns and the SWITCH measure [Values to Display] (#2 below) on Values.  The Display Value column has been sorted by the sort order column.

On another note, notice there are now blank rows in the matrix for Lights, Locks, Panniers, Pumps etc.  This is caused by the format string I mentioned above.  When there are no sales, the percentage change will return BLANK().  When the result is blank, it is hidden in the visual.  The FORMAT function has the effect of turning that blank into an empty string.  Empty strings are not hidden automatically, so that is why the blank line now appears. This can be fixed the following modification (line 24 below).

Now, before moving on, note the code in line 22 above.  Notice how I am able to write a single line of code inside the SWITCH that basically says “As long as it is one of the first 4 items, just give me the normal result to [Total Sales]”.  This is quite cool, because what I am effectively doing is leveraging the relationship between the header table and the calendar table for the first 4 items to do what it would do automatically without the header table.

Relationships or No Relationships

Technically it is not a requirement to use a relationship in such a header table. In this case I did because 4 out of the 6 results can be simply extracted from the base model automatically.  I could however have chosen not to create the relationship and instead hard coded each of the 6 results directly into the SWITCH measure.

There are 2 disadvantages of this SWITCH Measure/Header Table approach I have shown above.

1. Any formatting variations from the base measure must be formatted as Text as shown, with the implications that it can’t be used in a chart.
2. You have to write one SWITCH measure for every base measure you want to do this with, e.g. [Total Sales], [Total Quantity], etc. (Actually, as an aside, you could do some tricky nesting of a second switch measure and another disconnected header table to control the input base measure if you wanted to, but that would blow out this article too much).

Calculation Groups

The second, newer way you can solve this problem is to use calculation groups.  The main benefits of using calculation groups are:

• You can vary the number formatting depending on the column.
• You can make it work using any measure – not just [Total Sales].

The main downside is (as of now), you have to use Tabular Editor (a third party external tool) to create the calculation group. But hey, this is a great opportunity to learn how to do that.  If you haven’t used Tabular Editor before, I suggest you check out my article Introduction to Tabular Editor.

Here are the steps to complete this task in Tabular Editor

• Go to Tabular Editor
• Create a calculation group and name it, say, Sales Horizon
• Create calculation items in the calculation group
• Assign the ordinal values (sort order) for the calculation items
• Assign the format for each calculation item
• Save the model and switch back to Power BI Desktop
• Use the calculation group in your Report

Let me walk you through these steps.

Create a Calculation Group

Go to Tabular Editor.  You will find the tables in your data model in the left panel under the Tables folder. Right-click on the Tables folder to create a new calculation group. Name it as Sales Horizon (#1 below).

Create Calculation Items in the Calculation Group

This is where you will create the calculation items as shown in the table below.

To do this, I created the first calculation item as follows (right click on the Sales Horizon “table” and selected “new calculation item”. I named it 2001 (#1 below) and I wrote the DAX formula (#2 below).

Note, there are different ways you could write the formula for this calculation item.  I could have hard coded [Total Sales] instead of SELECTEDMEASURE(), but the latter allows a lot more flexibility in the use of this calculation group (I will explain that later).

Next, I created the calculation items for 2002, 2003, and 2004 (#1 below) by simple copying 2001 and pasting it 3 times, then changing the name and the formula accordingly (#2 below).

Next, I created the calculation item Chg 2004 vs 2003 as shown below.  The reason you can see question marks next to the calculation items above is that the calculation group does not get validated by Power BI until it is saved.  So I then clicked on the Save button. Note, I also clicked on the DAX Formatter button to format my formulas.

The last calculation item required is the % Chg. I wrote the measure as shown below.  Note how I reused the code from above.

Assign the Ordinal Values for the Calculation Items

The ordinal values of the calculated items define the sort order of the values in the visual. The ordinal numbers start from 0.

I changed the ordinal parameter for 2001 to 0 as shown below (#1 below).

Next I changed the ordinal numbers for 2002, 2003, 2004, Chg 2004 vs 2003, and % Chg to 1,2,3,4, and 5 respectively.

Assign the Format for Each Calculation Item

This is the last step in creating the calculation group and calculation items. It is possible to define the format of each calculation item separately as relevant. This is done in the property “Format String Expression” of the calculation item (#1 below).  I only did this for the last item in the list – the % Chg calculation.  By not setting the format for the other items, the base measure formatting will be used instead.

I saved the work in Tabular Editor and switched to Power BI Desktop. Power BI prompted a message to refresh the calculation group – I clicked the button Refresh Now.

Using the Calculation Group in a Report

After creating a calculation group as I have shown here, you will find a new table called “Sales Horizon” in the data model as shown below.

As you can see above, there is a column “Name” with the calculation items as values and the “Ordinal” column.

To build the visual below, I removed ‘Calendar’[Year] from columns in the visual and added Sales Horizon[Name]. I then added the measure [Total Quantity] to values in the visual.  Note that the correct number format displays for the quantity columns and the % column.

You can remove the [Total Quantity] measure and replace it with any other measure (such as [Total Sales], [Total Cost]) and those will work too. This is the benefit of using SELECTEDMEASURE() in the calculation group vs hard coding one specific measure.

Wrap Up

The demo in this article shows a simple example starting with 4 regular column values followed by 2 calculations from measures.  But in reality you can do what ever you like, including a complex P&L.  If you need to build a P&L, I suggest you take a look at my video on creating a P&L in Power BI here.