Sign in or start a free trial to avail of this feature.
6. Creating Tables and Matrices
In this lesson, we will see how to create tables and matrices. We’ll learn how to format these visuals, and how to drill up and down when one of the fields is a hierarchy.
- Tables contain a grid of data arranged as a series of columns
- Tables can be formatted like other visuals
- We can sort the table by any column by clicking the column header
- We can filter the table easily by selecting one or more rows, then including or excluding those rows
- Matrices allow us to add data as rows or columns
- When using a hierarchy in a matrix, you can drill up or down on either columns or rows
- This allows you to quickly analyze hierarchical data, but in a numeric, tabular format
When people think of Power BI, they generally think of elaborate graphical visualizations. However, there will come a time when you want to focus on numbers.
In this case, you'll likely find yourself using tables and matrices.
In this lesson, we'll look at how to create tables and matrices in Power BI.
The easiest way to create a table is to drag a text field onto the canvas.
Let's drag sales person to the canvas, followed by revenue, and then city.
Since the first field we dragged onto the canvas was a text field, Power BI automatically created a table.
We'll adjust this table by navigating to the values well, and changing the aggregation of city to count (distinct).
Let's take a moment to improve the formatting of this table.
Table style alters the general appearance of the table.
Note that when we select different options, the appearance of the entire table adjusts.
Let's stick with the alternating rows style.
Next, we'll open the grid option, and expand the text size from eight to 12.
Note that all text in the table increased in size.
There are many other formatting options available, but we'll leave the table like this for now. The table is currently sorted alphabetically by sales person.
If we want to sort another column, like revenue, we can simply click the column header.
All entries are now sorted by ascending order of revenue.
If we click it again, it sorts in descending order.
We can also easily filter rows in the table. We'll hold control, select multiple rows, then right click one of these rows.
We can either exclude the rows, which filters them out of the table, or include them, which filters out all other rows.
We'll exclude, and see that this shows up as a visual level filter on the filters pane.
We'll remove this filter, and move on to matrices.
Matrices are similar to tables, but allow you to create rows and columns of data.
They also have several more advanced features, which we'll see in the next lesson.
Let's select the matrix button in the visualization pane to create a matrix.
We'll then add location to the rows well, date to columns, and revenue to values.
As before, we'll navigate to the format section of the visualizations pane, select grid, and increase the text size to 12.
We'll also change the matrix style to alternate rows.
Our row and column fields are both hierarchies, which means we can drill down as we previously did with charts.
We'll turn on drill down, select 2016, to see quarterly data, and then select quarter three to see each month in that quarter.
We'll then select the south region, and the south atlantic sub region, to see each state.
Clearly, using hierarchies in a matrix allows you to quickly focus on a specific area of the business.
You may have noticed the drop down on the top left of the matrix, which options for rows or columns.
This determines if the drill up, show next level, and expand all buttons work on rows or columns.
Let's drill up through our date hierarchy.
We'll select columns, and then select drill up twice, bringing us back to the annual data.
Let's also drill back up through our regions.
We'll change the drill on drop down selection from columns to rows, and again select drill up twice.
The show next level and expand all buttons also use the same drill on drop down. Let's look an example.
We'll set the drill on drop down to columns, and then select show next level twice.
This moves us down to the month level of our date hierarchy, ignoring years and quarters.
We'll switch the drop down to rows, and this time click expand all twice.
This expands our location hierarchy down to the state level.
However, because we used expand all, regions and sub regions are still considered, and the matrix shows subtotals for these levels. Let's address the matrix to show only quarters and sub region and stop the lesson here.
In the next lesson, we'll continue our overview of the features available for both tables and matrices.