Visualizing tabular data

We will see that there are many options within Power BI to visually represent data, but sometimes our users may want to see and compare detailed data and exact values. In these scenarios, using the Table or Matrix visual ends up being our best choice. When leveraging either of these two visuals, it is important to take advantage of the Format area to ensure that users can easily interpret the detailed data that is being presented. One of the best ways to bring attention to values of importance with these visuals is by using Conditional Formatting. We will explore this option, as well as take advantage of the hierarchies we created in Chapter 3, Building the Data Model, to allow for drill downs within the visuals.

The table visual

The table visual is perfect for looking at many values (measures) for a category. To really make the table shine, we will also want to take advantage of the Conditional Formatting option that is available to us. In our example, we will be using the Sales Territory Region as our category and looking at four different values for it.

Let's look at, setting up the visual:

1. Rename the blank page we are working on from Page 1  to Tabular Data.
2. Select the Table visual and resize it to take up a little less than half the report canvas. Notice, similar to the slicer, that there is only one area in which to populate fields, called Values.
3. The first field we will want to select will be Sales Territory Region from the Sales Territory table; this will be our category.
4. Next, navigate to the Internet Sales table and select the Total Sales measure. Also, select the Profit, Total Cost, and Total Transactions measures. See Figure 5-7 for reference:

Figure 5-7

Already, we can see how this table provides great insights into our selected category, Sales Territory Region. By default, though, there are many formatting options that we will want to adjust. One of the first items will want to change is the size of the text for the data, as well as the headers. With the Table visual selected, go into the Format area (roller-brush icon) and expand the Column Headers section. We will see there are many options here for us, but for now let's simply adjust the Text Size option to something larger, making it easier to read the headers. Next, let's expand the Values area and make the same change here for the Text Size option. Now that our table is easier to read, let's explore the Conditional Formatting option, which will let us customize text or background colors based off values. If we return back to the Fields area where we can see our five options, you will note a small drop-down arrow next to each of our fields.

Select the arrow next to our Total Sales measure, and you will see the option for Conditional Formatting, as shown in Figure 5-8. When you place your mouse icon over the Conditional Formatting option, you will see that we are presented with three choices that are similar in functionality and setup. The one we will focus on is the Background color scales option, so go ahead and select that option. A menu will appear in which we will simply only change one option; place a check mark in the box that is in the bottom left that says Diverging. After hitting okay, we will now see that our Total Sales column is color-coded so that we can easily identify the regions that are good (green) and bad (red) performers. This is something that we can choose to apply to whichever columns we feel would benefit most from Conditional Formatting, but it is not necessarily required. With the use of this table visual, we can get a very quick and detailed understanding of performance for our Sales Territory Region category.

Note

It is important to also remember about Interactive Filtering with the table visual. Any of the rows that are present within the table can be selected, and will apply a filter to all other visuals on the same page.

Figure 5-8

 

The Matrix visual

Where a table does a great job of allowing users to consume tons of detailed data about a single category, the Matrix visual can accomplish this for more than one category. The Matrix visual allows users to not only select a category for the rows, but means we can also select a field to populate the columns, which allows us to see detailed data at a cross section for two categories. Conditional formatting is also available for use within the Matrix visual, and is incorporated in the same fashion as we accomplished in the previous example. Other than Conditional Formatting, the Matrix visual can take advantage of established hierarchies to give users that capability of drilling down into more granular data. Many of the other visuals can also take advantage of hierarchies, but for tabular data the Matrix visual does a great job with this.

Let's look at, setting up the visual:

1. Ensure that you do not have any other visual selected, and choose the Matrix visual from the visual's area.
2. Firstly, we will populate the Rows area with our Sales Territory Drilldown, which you will find under the Sales Territory table. You will see that when we place a check mark next to the Sales Territory Drilldown option, it brings in three different fields, starting with Sales Territory Group.
3. Next, we will select what is known as a natural hierarchy which Power BI automatically creates from our Date (Order) table. Place a check mark next to the Date field, which will bring in the Year, Quarter, Month, and Day fields. If these fields do not automatically become populated in the Columns area, just drag them to that location. Now, we have our two categories with options to drill down to get more granular data.
4. Lastly, let's add two measures to the Matrix under the Values area. Select both the Total Sales and Profit measures. See Figure 5-9 for reference. Move and resize the Matrix as you see fit:

Figure 5-9

Now, we can see that the amount of data available to us is even greater than that of the Table visual. We should apply the same format changes to the Text Size as we did for the table at this point. The Matrix allows us to see fantastically detailed information about the different geographic regions, as well as a breakdown per year. Also, you will see that there are some new icons in the upper left of the visual which relate to the drilldown feature we spoke of earlier. Because we have hierarchies on both the rows and columns, we must decide which we would like to expand for further details. We will focus solely on the rows option and expand upon the geographical category. The first upward pointing arrow, which should be currently grayed out, allows users to move up a level in the hierarchy, but we are currently at the highest level. The option just to the right of this, which is depicted by two disconnected downward arrows, will change the category to the next level of the hierarchy, which is the Sales Territory Country in our example. Let's go ahead and select this option two times so that we are displaying the Sales Territory Region. The third option, which is depicted by two connected downward arrows will also go down one level at a time through the hierarchy, but it will also retain the previous (higher) category. By having the Matrix and Table next to each other, you can see the difference in detail that can be achieved by each of them. Both, though, can benefit greatly from Conditional Formatting.