7 Secrets of the Matrix Visual

By Alison Box

Power BI in Practice

Some of you Excel users may have realised that the Power BI Matrix visual is just an Excel Pivot Table by another term. You even get buckets for “Rows”, “Columns” and “Values” just like constructing an Excel Pivot Table.

The temptation is to think this is all the Matrix visual can do; act like, well, a typical Pivot Table: –

Excel scores of the week per contestantPivot Table for Power BI - how to

Excel Power Pivot Table details


However, depending on your knowledge of Excel Pivot tables, you’ll know that if the Matrix is just like an Excel Pivot Table, then you should be able to do a lot more than just show a “matrix” of data. The trouble is that unlike the Excel Pivot Table, where all the options are readily at hand on the Excel Ribbon, most of the Matrix options are so hidden away they could be described as “secrets”.

Next upcoming course




Power BI Fundamentals (Online)


Thursday, 9 December 2021 - Friday, 10 December 2021

10:00 AM - 04:30 PM (2 days)

  • Online
  • £595.00 excl. VAT

Thu 9 Dec 2021 - Fri 10 Dec 2021

10:00 AM - 04:30 PM (2 days)

Delivered live online over Zoom, our two-day Microsoft Power BI Fundamentals training course is the perfect introduction to using Power BI

More Information

With this in mind, here are my seven favourite Matrix secrets.

Secret No.1 – Grand Totals are really Subtotals.

In an Excel Pivot Table, Grand Totals are easily removed (just use the button on the Ribbon labelled “Grand Totals”). In a Power BI Matrix, you might think you’d be able to do the same thing on the Grand Totals card, but you won’t see any option to turn them off. This is what I mean by “secretive”. Where is this option hidden? Well, one thing you must understand is that in a Power BI Matrix, Grand Totals are really Subtotals! So to turn Grand Totals off, use the Subtotals card.

However, be warned; if you have multiple fields in the Rows bucket, this will remove all your Subtotals, not just the Grand Total. To remove only the Grand Total while retaining other Subtotals, you’ll need to discover another secret, No.4 below.

Scores of the Week Per Contestant Visual Matrix on Power BIRemove subtotals and Column subtotals in Power BI


Secret No.2 – Expand all down one level in the hierarchy.

The starting point to understanding this secret is to appreciate that it’s not until you work with multiple fields in the “Rows” bucket that your Matrix can become more informative. You’ll see below that I now have both Contestant and Professional in Rows.

Multiple fields in the Rows bucket on Power BiExpand all rows one level in the hierarchy

Unfortunately, when you put a second field into Rows, it may appear that nothing much has happened; secretive or what! However, you’ll notice a series of arrows at the top right (or bottom right) of the visual. There is one label “Expand all down one level in the hierarchy”. Apparently, this is what you need to click on to see the second row.

how to review irrelevant subtotals - graph showing subtotals in the wrong place

Looking at the Matrix above, not exactly what I want though. The Professional names are indented under each Contestant when they really should be sitting beside each other, and because there is only one Professional for each Contestant, I get irrelevant Subtotals on the Contestant rows (in bold). More secrets need revealing!

Secret No.3 – Turn off “Stepped layout”.

Turn off stepped layout in Power BI

With most Matrices, the indented or “Stepped” layout gives less clarity to which rows the values refer to. It’s better to put each Row label in a separate column. To do this, just turn off “Stepped layout” on the Row headers card (cf. “Compact layout” of the Excel Pivot Table).

Turning Stepped Layout Off in Power Bi

This is definitely an improvement, but I still see those subtotals and they’ve now moved to a row of their own!

Turn off Row subtotals on the subtotals Card on Power BI

Clearly, I can now just turn off the Row subtotals on the Subtotals card (as explained in Secret No. 1 above.)

Secret No.4 – Only show the Subtotals/Grand Totals you want to see.

Subtotals and totals in Power BI

However, if I add a third row label e.g. for the Judges, things become a little more challenging. I now want to show the subtotals, but only for the Judges’ Scores. If I turn subtotals on, I get subtotals for both Professionals and Judges, showing the same values.

adding third row labels to Power BI

So how do I control which Subtotals and/or Grand Totals to show? This secret is really hidden away where no one can find it!

Subotals card per row level and per column option on Microsoft Power BI Per row level changes for Power BI Matrix Tables

So this is where you will find it. On the Subtotals Card, at the bottom, there is a “Per Row Level” and a “Per Column Level” option. Turn these on.

Then select the field(s) that you want to show Subtotals or Grand Totals for (note the Grand Total field is listed first). I found “trial and error” worked well here!

Secret No.5 – Show Values on Rows

Ever wanted to do this? Compare the two Matrix visuals below. On the left, the Values are in columns, which is the default. On the right, the values have been placed in the Rows. This is a well-known secret, but all you need to do is expand the Values card and select “Show on rows”

Show Values on Rows with Power BIShow on Rows toggle on Power BI

Secret No.6 – Show text in the Values area.

Ever wanted to put the names or descriptions into the values area of the Matrix, as in the example above. You can see that in Rows is Contestant names, in Columns is the Week and the values area is populated with the names of the dances performed.

For this you need a simple measure as follows: –

Dance Name = SELECTEDVALUE(Dances[Dance])

In the brackets of the SELECTEDVALUE function, put the column name of the column you want to put in the middle of your Matrix and use this measure in the Values bucket.

However, just to say that if there is more than one dance in a particular week, then the dance won’t show so this is why I needed another Matrix to show multiple dances per week.

Secret No.7 – Use Conditional Formatting.

Compare the 2 visuals below. See how much more appealing and informative the coloured chart is. Just using a simple background color brings the visual to life.

Contestant Conditional Formatting Background Colour On in Power BI
Conditional Formatting Average Cll Value is Greater Thsk (Background colour changes) On in Microsoft Power BI

Contestant Conditional Formatting not included in Microsoft Power BI



















Matrix Visuals in Power BI are extremely useful when it comes to displaying information clearly and wanting to drilldown into the numbers. We cover a range of visualisations on both our Fundamentals and Advanced courses, meaning you can get in depth knowledge on exactly how to use these functions to your advantage. If you would like more information about our courses, get in touch with our team on 0800 0199 746.

Next upcoming course




Power BI Fundamentals (Online)


Thursday, 9 December 2021 - Friday, 10 December 2021

10:00 AM - 04:30 PM (2 days)

  • Online
  • £595.00 excl. VAT

Thu 9 Dec 2021 - Fri 10 Dec 2021

10:00 AM - 04:30 PM (2 days)

Delivered live online over Zoom, our two-day Microsoft Power BI Fundamentals training course is the perfect introduction to using Power BI

More Information

Discover our Power BI courses

Update – an 8th Secret!

If you liked this, be sure to check out my update to the Matrix secrets, with

The 8th Secret of the Matrix Visual


  1. For #6, please elaborate on how SELECTEDVALUE() is able to pull out the column heading/title. From my understanding, the function pulls out the value in the column and not the heading. Have tried it on my own dashboard but to no avail. Would appreciate further clarification.

    • You’re quite correct in describing the SELECTEDVALUE function. It retrieves a value from a column in a dimension that has been filtered down to one row. The function will return a blank by default if there is more than one row filtered. In the data described in #6, for each evaluation of the Values area of the matrix, the Dances table has been filtered to one row containing data for a single contestant, each row containing Contestant Name, Week No. and Dance and a number of other columns. In the #6 above, SELECTEDVALUE finds the value in the Dances column for the Contestant in the current filter context. If your expression returns nothing it will be because you have more than one value in the current filter context.
      I hope this clarifies Secret #6

  2. Hi Alison,

    You mentioned, “this is why I needed another Matrix to show multiple dances per week.” Please elaborate which Matrix you used for this situation?


    • I just put Contestant, Week No. and Dance all into the Rows bucket with no value and turned off the stepped layout.

    • I’m not sure why you would want to do this and I don’t know anything about your data. However, if we take the matrix that has Contestant on rows, Weeks in columns and Scores in Values, if you wanted not to see the total for Week 2, you would use something like this in DAX:-
      Not Week 2 = IF(SELECTEDVALUE(Week[Week])=2 &&
      [Total Scores])
      The thing is you would need to use DAX and be specific about which totals you don’t want to see.
      Hope this helps

Leave a Reply

Your email address will not be published. Required fields are marked *