By , Senior Technical Consultant, AKA Enterprise Solutions
Power BI provides many ways for you to view and analyze your data with visualizations. Several visualizations come with , including bar, scatter, and line charts, pie and donut charts, and more. There are even more visualizations available for download that have been contributed by the Power BI community.
Microsoft has just added “matrix” visualization to help users analyze hierarchical data, such as parent-child relationships within companies that have departments and divisions.
Here we will look at how to use matrix visualizations with sales data from a typical ERP or CRM system like .
The image shows “Product Sales by Region” and shows sales data for two countries, the United Kingdom and the United States. Matrix visualization allows users to drill down into each country by state, city, and zip code.
Use matrix visualization to look at Product Sales by Region to see sales by country, state, and locale.
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.
This 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.
In the image below shows the setup for matrix visualization, with the data fields Country, State or Region, City, and Postal or Zip Code. We have also selected Tax and Total as additional number values to display. This can be set up using drag and drop from the data.
Matrix setup showing Country, State or Region, City, and Postal or Zip Code
Looking more closely at the actual matrix, notice there are two buttons: “Go to next level” and “Expand one level.”
Choose display options: “Go to next level” and “Expand one level”
By selecting “Go to next level,” the visual is redrawn to show data based on States or Regions. For example, to view the sales by state or region, the user would see data as shown here:
Viewing data by state or region
Drilling deeper will show City data for a specific state or region, such as California in this example:
Viewing data by cities in California
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:
Choose the Up arrow to move up a level or back to the top
Selecting “Expand one level” button displays the expanded matrix, useful for seeing 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:
Viewing summary data using “Expand one level”
Users can continue expanding levels up to the number of levels established in the matrix.
Expand levels up to the number of levels in the matrix
Right-clicking the visual offers options such as include (show only) or 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:
Right click to choose data view options
Discover trends by using cross-highlighting. 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.
Use cross-highlighting to discover trends
Finally, users can customize the look of the matrix by adjusting features such as the matrix styles, colors, fonts, conditional formatting, and more.
Change the look of the matrix by style, font, color, and more
Take the time to experiment with matrix visualizations in Power BI. It is a welcome addition to an already powerful program.
Would you like to learn more about Power BI? Are you ready to discuss a strategy for more powerful business intelligence and data analytics?
