Discover a New Feature in Microsoft Power BI: Matrix Visualizations

By Carl de Souza, Senior Technical Consultant

Microsoft’s Power BI is a powerful tool that gives you multiple techniques for viewing and analyzing data. Data can be viewed using any of the various charts and graphs that come with the program, and even more visualizations are available for download from the Power BI community.

Microsoft has recently added the matrix visualization to help analyze parent-child relationships, such as those found within companies with departments or divisions.

This blog demonstrates how to use matrix visualizations to look at sales data from a standard ERP or CRM system Like Microsoft Dynamics 365.

Figure 1 shows “Product Sales by Region”, which has sales data for the United Kingdom and the United States. Use matrix visualization to drill down into each country by state, city, and zip code.

Figure 1 – Use matrix visualization to look at Product Sales by Region to see sales by country, state, and locale.

The column on the right shows the available visualizations, including “matrix”. The arrow on the left points to the actual matrix.

Also shown is a bar chart with the number of products ordered from each country and a map showing where the sales occurred along with a circle representing the relative size of the sales.

Figure 2 shows the setup information, such as Country, State, and Zip Code. Tax and Total were also selected for display. Fields can be added using drag and drop from the data.

Figure 2 – Matrix setup showing Country, State or Region, City, and Postal or Zip Code

Two buttons are available in the matrix: “Go to next level” and “Expand one level”, shown in Figure 3.

Figure 3 – Choose display options: “Go to next level” and “Expand one level”

Select “Go to next level” to redraw the visual to show data based on States or Regions, as shown in Figure 4:

Figure 4 – Viewing data by state or region

In Figure 5, we drill deeper, in this case showing City data for California:

Figure 5 – Viewing data by cities in California

Matrix visualization supports many levels of granularity, depending on how the matrix is configured.

Select the top left arrow to move back up a level or back to the top, as shown in Figure 6:

Figure 6 – Choose the Up arrow to move up a level or back to the top

Expand the matrix with the “Expand one level” button to see summary data from the previous level. The example in Figure 7 shows the total for the United States along with the totals of each state below it:

Figure 7 – Viewing summary data using “Expand one level”

Users can continue expanding levels up to the number of levels established in the matrix:

Figure 8 – Expand levels up to the number of levels in the matrix

By right-clicking a visual, users can choose from options shown in Figure 9. For instance, users can include (show only) or exclude (remove) certain data from the view, useful to limit the report to only show data from sales in the United States:

Figure 9 – Right click to choose data view options

Cross-highlighting allows users to discover trends. For example, by selecting California as the State, the map is redrawn to show exactly where the sales are occurring, as well as showing which products are responsible for these sales, as shown in Figure 10. This information can be used to develop strategies based on regions and product or to analyze the effectiveness of marketing strategies:

Figure 10 – Use cross-highlighting to discover trends

Users can easily customize the look of the matrix by adjusting features such as the matrix styles, colors, fonts, conditional formatting, and more.

Figure 11 – Change the look of the matrix by style, font, color, and more

Matrix visualization is a powerful feature to Power BI, and it is well worth the time to familiarize yourself with its capabilities. Want to learn more about Power BI? Watch this informative webcast, Explore Power BI in Just One Hour, and contact us to discuss your analytics needs.

Did you find this blog informative? Read more of Carl de Souza’s blogs at CarldeSouza.com

The Case for Financial Management & ERP in the Cloud: A Guide for Skeptics and Advocates

Download the eBook!

Microsoft Dynamics ERP in the Cloud

Discover Microsoft Dynamics 365

Watch our webcast series!