Configuring table and matrix visuals - Power BI Tutorial

- [Instructor] We often think of Power BI as the eye-catching data visualizations we see in marketing materials. However, behind these visuals, there's often a great deal of modeling and calculations we need to set up beforehand. One of the ways in which I personally test the models I'm developing in Power BI is by putting the numbers and calculations in tables first. Tables and matrices are actually standalone visuals on their own within Power BI as well. Let's first create a tabular pivot table by selecting the table visual. We'll then add the state from the state table followed by the population from the populations table. We can either drag the field to the columns or we can select them in the field list. We see that within the table visual, all 50 states appear as individual labels for each row. This is a dimension field in the visual because we're using it as a field to group the aggregated results by. Even though our data is down to the granularity of the county level, we can roll up the total population across all the counties within each state. The aggregated results in this table represent the population totals for all the counties within a state but they also over-represent the actual population because this is the sum of the population total layup over all 50 plus years in which we have population data. This table gives us a high level summary of the state populations. Power BI defaults to using a sum aggregation when we add a numeric field to the visual. However, we can change it to another aggregation directly in the menu options for that field. Let's make the population an average instead of a sum. This gives us an average population for each state over a 50 plus year time period. However, it's not particularly insightful to calculate the average over all the counties that we have population data for because there are a lot more counties with lower populations than higher populations, which will skew the overall averages quite a bit. Let's change our aggregation back to sum instead. However, in order to see accurate populations by year, we need to make this table actually more granular. For this particular data model, one way we do that is by adding a year field as another column in our table. This changes the granularity of the total automatically because we added another field. The population totals now accurately reflect the population numbers for each year and state because the table summary matches the aggregation level of our data in this example. We also notice that adding the year automatically adds four date components to the table. We don't need to include the quarter, month, or day in the visuals, so we can remove these three fields but we'll leave the year component in the visual. Later in this chapter, we'll explore how to use hierarchies like this in our Power BI model. This table visual though for the population summary is kind of hard to read because we have so many rows. Let's instead use a matrix visual to display this population summary. With the table selected, let's choose the matrix option from the standard visuals to convert it directly into a matrix visual. This changes the pivot coordinates for the table. We don't see the year field anymore but rather we see plus icons next to each state label. This feature we find on matrix visuals is one of the reasons it's my favorite way to view tables. It has a familiar feel to Excel pivot tables because it enables us to expand and collapse aggregated totals within each row. However, we're going to put the year in the columns instead so that we can see the accurate population totals for each state in chronological order within each row. We can either remove the year entirely or we can move it directly into the columns instead. Notice how this new table structure makes it easier to see a summarized view of the populations over time for each state, because while there are now more columns, there are a lot fewer rows than we saw in the table visual. It doesn't change the population totals because it still evaluates the population total at the same pivot coordinates for both the year and state level. One thing that I really like about matrix visuals is that we can add additional labels to the rows and columns by adding new fields to them. If we add the quarter to the columns, we see icons for drill down options appear at the top of the visual. If we expand them though, the increased granularity for the date doesn't change the total because the US census only shares population totals once a year. I really like how Power BI automatically adds expansion icons to the rows when there's more than one field in them so let's add the county name to the rows after the state. We see how the county population totals roll up to the state level for each year. This gives us insights not only into the most populous counties within the states but also how the population trends over time impact the overall state population.