Visualize Your Data with the New Matrix for Power BI

Visit Website View Our Posts

By Carl de Souza, Senior Technical Consultant, AKA Enterprise Solutions

Microsoft Power BI contains many visualizations to help you view your data in different ways. Out of the box, Power BI includes bar, column and line charts, scatter charts, pie and donut charts, funnels, gauges and many more visualizations. It also has a large number of interesting visualizations built by the Power BI community that are easily downloadable.

Microsoft has now included a “matrix” visualization to help users drill into and interact with their data more easily. It’s a simple concept that is powerful and easy to use. This new visualization is particularly useful when it comes to visualizing and interacting with hierarchical data. Examples of such data include parent-child companies with departments and divisions, and any data that’s categorized like types of customers.

Let’s take a look at how we can use the new matrix visualization with sales data from an ERP or CRM system such as Microsoft Dynamics 365.

Following is a Power BI report showing “Product Sales by Region.” It shows sales data for 2 countries, the United Kingdom and the United States. It also shows which regions are selling which products, and where exactly they are sold. Being able to see sales by country is useful, but what is more powerful is being able to drill into each country, to their state, city, and zip code, and have the report and numbers redraw contextually.

The arrow to the right shows how to select the new “matrix” visualization (under Visualizations), and the arrow to the left is the actual matrix.

The report also contains a bar chart showing the number of products ordered from these countries, and a map of where the sales are occurring, with the map circle size noting the size of the sales.

Looking at the matrix setup below, it shows the data fields Country, State or Region, City and Postal or Zip Code. This is also the data hierarchy from top to bottom. We have also selected Tax and Total as the number values we want to see. Setting this up is as simple as dragging and dropping from our data:


Now let’s look more closely at the matrix itself. There are 2 buttons enabled, one showing options to “Go to next level” and one showing “Expand one level.”


If we select “Go to next level,” the visual redraws to show us data based on each of the States or Regions. Let’s say the report user wants to know what the sales were like for California. Drilling down this way would show the data:

We can then go further by selecting the same button to drill into City data:


We can get as granular as we need to, depending on how we configure the matrix. Selecting the top left arrow allows us to move back up a level or back to the top:

If we select the “Expand one level” button, it will display the matrix expanded, which is useful if you want to see the previous level and summarized numbers, such as the total for the United States as well as the totals for each of the States below it:


You can further expand to as many levels as are set up in the matrix:

Right-clicking on the visual is also supported, with the ability to include (show only) exclude (remove) certain data. This may be useful if you want to limit the report, for example to only show sales in the United States:

The matrix visualization also supports cross-highlighting, which is particularly useful when interacting with reports to discover trends. For example, selecting California as the State in the matrix will redraw the map to show exactly where the sales are occurring, as well as showing which products are responsible for these sales. Corporate strategies could then be determined based on which regions and products are performing; add marketing data to determine how marketing strategies are complementing sales and you have a powerful report.

Finally, if you want to customize the look of the matrix visual, there are plenty of features you can play around with, including the style of the matrix, colors, font, conditional formatting etc.

The matrix is a great new addition to the Power BI visualizations family. And as with other Power BI features, look out for it to get even better over time.

Would you like to learn more about Power BI? Are you ready to discuss a strategy for more powerful business intelligence and data analytics? Contact the experts at AKA Enterprise Solutions.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Show Buttons
Hide Buttons