Making matrices behave more like Excel pivot tables

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Power BI Desktop Update for May 2024 Part two of a seven-part series of blogs
This month's bigger than average update sees new pivot table style matrix layouts, the emergence of modern tooltips, calculation groups and the DAX query pane from preview and a couple of other smaller changes. Power BI Desktop Update for May 2024 Making matrices behave more like Excel pivot tables (this blog) Modern tooltips are now turned on by default DAX query view has emerged from preview Model Explorer and Calculation Groups are now generally available More line formatting enhancements A redesigned Manage Relationships window We've been creating our idiosyncratic monthly blogs on Power BI updates since November 2016, and also deliver online and classroom Power BI courses.

Power BI Desktop Update for May 2024

Part two of a seven-part series of blogs

This month's bigger than average update sees new pivot table style matrix layouts, the emergence of modern tooltips, calculation groups and the DAX query pane from preview and a couple of other smaller changes.

Power BI Desktop Update for May 2024
Making matrices behave more like Excel pivot tables (this blog)
Modern tooltips are now turned on by default
DAX query view has emerged from preview
Model Explorer and Calculation Groups are now generally available
More line formatting enhancements
A redesigned Manage Relationships window

We've been creating our idiosyncratic monthly blogs on Power BI updates since November 2016, and also deliver online and classroom Power BI courses.

Posted by Andy Brown on 04 June 2024

In this blog

How to change the layout of a matrix
Repeating row headers
Including blank rows

You probably already know and love the matrix visual:

A typical matrix, showing sales by environment and habitat (on the left) and centre type (across the top).

Well, Microsoft are now trying to make it behave more like a pivot table:

The same thing, but as a pivot table in Excel with the report layout options shown.

I'm not totally convinced that this is a good thing. The ethos of Power BI Desktop was to start with a completely blank slate - it's a slippery slope if Microsoft start trying now to shoehorn it into an Excel mould.

How to change the layout of a matrix

It's easy enough to change the layout of a matrix in Power BI Desktop:

You'll find this Layout dropdown in the matrix's formatting properties.

Here's what the 3 different views look like for a typical matrix:

In order: Compact, Outline and Tabular view,

Repeating row headers

Staying with the Excel pivot table theme, you can now choose to repeat row headers in a matrix, provided that you choose the Outline or Tabular layouts:

The option to repeat row headers.

Here's what this will give for one of our examples:

The only thing I ever used this for in Excel was to copy the data; I'm not clear why I'd want to do this from Power BI.

Including blank rows

I should have led with this, as I think it's by far the best new change:

You can now choose to separate sections with blank rows.

I like this!

The sections are much easier now to distinguish.

To use Microsoft's words, "It’s a thoughtful addition that brings a new level of clarity and organization to Power BI’s matrix visuals".

Parts of this blog
Power BI Desktop Update for May 2024 Making matrices behave more like Excel pivot tables (this blog) Modern tooltips are now turned on by default DAX query view has emerged from preview Model Explorer and Calculation Groups are now generally available More line formatting enhancements A redesigned Manage Relationships window