33.matrix Visualization

Matrix visualization
Introduction
When you are presenting tabular information in a Microsoft Power BI report page or dashboard, it
is important that the key results and insights do not get lost in a flood of information. Large blocks
of data in rows and columns can often be difficult for people to read and assimilate. The Matrix
visualization in Power BI is a powerful tool that gives the report viewer control over how much or
how little information they see in the table. It also allows them to interactively generate focused
totals and results as required.
What is a matrix visual?

The matrix visual is similar to a table but has key features that allow the report designer to
communicate multiple levels of information in the data. A table supports two dimensions, and the
data is flat, meaning duplicate values are displayed and not aggregated. On the other hand, a
matrix makes it easier to display data meaningfully across multiple dimensions because it
supports a stepped layout. Unlike the table, the matrix automatically aggregates the data,
enabling the viewer to drill down into the detail.
When you create matrix visuals in a Microsoft Power BI report, you can cross-highlight elements
within the matrix with other visuals on that report page. For example, you can select rows,
columns, and even individual cells and cross-highlight. Also, individual cells and multiple cell
selections can be copied and pasted into other applications.
There are many features associated with the matrix, which you’ll explore in the following sections
of this reading.
Understanding how Power BI calculates totals

Before using the matrix visual, it's essential to understand how Power BI calculates total and
subtotal values in tables and matrices. When creating total and subtotal rows, Power BI does not
just perform a simple addition of the values in the visible or displayed rows. Instead, it evaluates
the measure over all rows in the underlying data, which means you can end up with different
values in the total row than you might expect.
Let’s explore the following visuals.
In this example, each row in the visual displays an amount for each order status combination.
What is immediately apparent is that the figure in the total row does not seem to be correct for
the entries in the column above. Because an order status shows up against multiple regions,
their totals appear more than once. This is why the accurate total from the underlying data in the
total row and a simple addition of the visible values do not match. This is a common pattern
when the value you’re summing is on the ‘one’ side of a one-to-many relationship.
When you examine totals and subtotals, remember that those values are based on the
underlying data. They aren't solely based on the values visible in the rows above.
Expanding and collapsing row headers

There are two ways you can expand row headers. The first is through the right-click or context
menu. There are options to expand the specific row header you selected, the entire level, or
everything down to the very last level of the hierarchy. There are similar options for collapsing
row headers as well.
You can also add +/- buttons by selecting them in the Row Headers card in the Format pane. By
default, the icons will match the formatting of the row header, but you can customize the icons’
colors and sizes separately if you want. Once the icons are turned on, they work in a similar way
to these icons in Excel PivotTables.
The expansion state of the matrix will be saved when you save your report. A matrix can be
pinned to a dashboard in an expanded or collapsed form. When that dashboard tile is selected
and the report opens, the expansion state can still be changed in the report.
Drill down on row headers

When you add multiple fields to the Rows section of the Fields well in the Visualizations pane, you
also enable drill-down actions on the rows of the matrix visual. This is similar to creating a
hierarchy. You can drill down and then back up through that hierarchy and analyze the data at
each level.
In the following image, the Rows section contains Sales stage and Opportunity size, creating a
grouping (or hierarchy) in the rows that you can drill to view details.
When the visual has a grouping created in the Rows section, the visual itself displays the Drill
and Expand icons in the top corner of the visual.
Similar to the drill and expand behavior in other visuals, selecting those buttons allows you to drill
down (or back up) through the hierarchy. In this example, selecting the Drill down one level icon
(the pitchfork) drills down from Sales to further details by Months, as displayed in the following
image.
Another way to drill down is to select any row header and choose from the menu that appears.
Selecting Drill down from the menu that appears expands the matrix for that row level and
excludes all other row headings except the row header that was selected.
In the following image, Cancelled > Drill down was selected. Notice that other top-level rows no
longer appear in the matrix. This is a useful drill feature, particularly for cross-highlighting.
Select the Drill up icon to get back to the previous top-level view. If you then select Cancelled >
Show next level, you get an ascending listing of all the next-level items (in this case, the Month
field) without the higher-level hierarchy categorization.
Select the Drill up icon in the upper corner to have the matrix display all top-level categories,
then select Cancelled > Expand to next level to view all the values for all levels of the hierarchy
Order Status and Month.
You can also use the Expand menu item to control the display further. For example, select the
Drill up icon. Then select Cancelled > Expand > Selection. Power BI displays one total row for
each Sales stage and all the Opportunity size options for Proposal.
Drill down on column headers

Just as you can drill down on rows, you can also drill down on columns. In the following image,
there are two fields, Region and Order Date Month, in the Columns field well, creating a
hierarchy. As soon as the second field is added to Columns, a new dropdown menu choice
labeled Drill on is displayed on the visual to the left of the drill and expand icons. It currently
shows Rows.
To drill down on columns, change the selection on the Drill on menu to Columns. Then select the
Europe region and choose Drill down.
When you select Drill down, the next level of the column hierarchy for Region > Europe displays,
which in this case is Monthly Order Status. The other region is hidden.
The rest of the menu items work on columns in the same way they do for rows (see the previous
section, Drill down on row headers). You can Show next level and Expand to next level with
columns just as you can with rows.
Note: The drill-down and drill-up icons only apply to rows. To drill down on columns, you must
use the right-click or context menu.
Stepped layout with matrix visuals

The Matrix visual creates a Stepped layout by automatically indenting subcategories in a
hierarchy beneath each parent. In the original version of a matrix visual, subcategories are
shown in a different column, which takes up space in the visual, as shown in the following
image.
However, the following image displays a matrix visual with Stepped layout. Notice that the
category Month has its subcategories (February, March, and April) slightly indented, providing a
cleaner and more condensed display.
Formatting the Stepped layout

You can easily adjust the Stepped layout settings. Select the Matrix visual, and then, in the
Format section (the paintbrush icon) of the Visualizations pane, expand the row headers section.
There are two options here. The Stepped layout can be turned on or off using the toggle. The
indentation amount in pixels can be set in Stepped layout indentation.
If you turn off the Stepped layout, Power BI displays the subcategories in another column rather
than indented beneath the parent category.
Subtotals and grand totals with matrix visuals

You can turn subtotals on or off in Matrix visuals for both rows and columns. In the following
image, the row subtotals are set to On and to display at the bottom.
When you turn on Row subtotals and add a label, Power BI also adds a row and the same label
for the grand total value. To format the Grand total, select the format option for Row grand total.
If you want to turn subtotals and the grand total off, in the format section of the Visualizations
pane, expand the Row subtotals card. Turn the row subtotals slider to Off. When you do so, the
subtotals aren't displayed.
The same process applies to column subtotals.
Conclusion
The Matrix visualization is a dynamic tool in Microsoft Power BI that can enhance the Reports
and Dashboards and provide a more exciting overall experience. By using a Matix visualization, a
Power BI analyst can include a large amount of data that is relevant to diverse audiences. They
can do this, secure in the knowledge that the individual users can interact with the data in a
dynamic way and “drill down” to find the specific answers to the questions they are asking.

33.matrix Visualization

Uploaded by

Copyright:

Available Formats

33.matrix Visualization

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

33.matrix Visualization

Uploaded by

Copyright:

Available Formats

Matrix visualization

What is a matrix visual?

Understanding how Power BI calculates totals

Let’s explore the following visuals.

Expanding and collapsing row headers

Drill down on row headers

Drill down on column headers

Stepped layout with matrix visuals

Formatting the Stepped layout

Subtotals and grand totals with matrix visuals

The same process applies to column subtotals.

You might also like