Sign in or start a free trial to avail of this feature.
7. Formatting Tables and Matrices
In this lesson, we’ll continue looking at matrices. We’ll see how to use several conditional formatting options, how to use these visuals in interactions, and how to show values as a percentage of totals.
- There are several conditional formatting options available for matrices:
- Data Bars: Data bars add a bar to each cell, sized according to the number in the cell
- Background Color Scales: Changes the background color of the cell according to its value
- Font Color Scales: Changes the font color of a cell according to its value
Showing Values as Percentages of Totals
- With any visual, numbers can be shown as a percentage of the grand total
- This can be useful if you are more interested in proportional contributions than in absolute numbers
- In matrices, numbers can also be shown as a percentage of the row total or column total
In this lesson we'll continue our look at tables and matrices by examining more of their features.
We'll look at conditional formatting, interactions and changing how values are displayed.
When you use a table you generally expect that viewers will have time to read and process the numbers within in.
However, you may still want to convey a key message at a glance.
We can do this with conditional formatting.
Let's start by formatting the table we created in the previous lesson.
In this case we'll apply conditional formatting to the revenue field.
To that end we'll navigate to the value as well.
Click on the arrow next to the revenue field and select conditional formatting.
We can see there are three types of conditional formatting available. Background color scales, font color scales, and data bars.
Let's select data bars.
This formatting option adds a bar to each revenue cell based on the value.
Note that we can specify the color of positive and negative bars.
As sales people only generate positive amounts of revenue, we won't have any negative bars.
The show bar only option removes numeric values from the table and shows only these data bars.
We can also set the maximum and minimum values for our formatting.
For example if we set the minimum at 300,000 we wouldn't see bars over the revenue values for Barkus and Stefanie.
We'll leave the options as they are and press okay.
As we can see each revenue cell now has a bar allowing us to visually compare revenue figures for each sales person.
If we mouse over a data bar we get a tool tip showing the number it represents.
Next, we'll format the matrix.
We'll select the matrix and again open up the conditional formatting options for the revenue field. This time we'll select background color scales. Background color scales formats the color of each cell based on it's value. Similar to color saturation as we saw previously. We'll leave the minimum color as red and change the maximum color to green.
We'll select color, then custom color and select a bright green.
We'll also select the diverging box and set the midpoint color as white. Finally we'll change the format blank values box to don't format.
We'll press okay, and see the cells in the matrix are formatted according to their value.
Lower values are red and higher values are green.
This make it easy to identify areas and times of the year that generate high or low amounts of revenue.
Let's take a moment to investigate interactions between tables and matrices.
We'll select the salesperson, Page, in our table and see that the figures in the matrix adjust accordingly.
Similarly if we deselect page, then select a quarter from the matrix, the table adjusts to show only sales from that quarter.
We'll deselect the quarter to show all values. The numbers in the table on matrix currently represent revenue figures. This can be difficult to compare between sales people. To address this we'll select the table, click the arrow next to the revenue field and the value as well, select show value as, and select percent of grand total. Now instead of showing revenue figures each cell shows a percentage of revenue generated by each sales person. This makes it easier to identify and compare the contribution of each sales person to the companies total revenue. This is quite a powerful option and can be used in any visualization, not just tables and matrices.
It's ideal in situations where a percentage is more useful than a raw number. The show value as options are more extensive for matrices.
Let's select the matrix and open up show value as options for revenue.
We can show value as a percentage of grand total, the row total, or the column total.
Let's select percent of column total.
The value in each cell is now a percentage of revenue for a quarter generated by Sebrigen.
For example, in quarter two, the mid atlantic region generated 27.91% of all revenue generated that quarter. Using row or column totals can often be more informative than seeing a percentage of grand total as the value of each cell can be a very small percentage of the grand total. This concludes our overview of tables and matrices in Power BI. As you can see tables and matrices can provide an ideal way of obtaining numerical insights from your data.
In the next lesson we'll look at how we can use quick measures to add data to our model.