Matrix Chart based on SQL Server Database

 Power BI Matrix chart also called as Cross tab, is useful to display relationship between two or more than two groups.

    In this article I will be creating a Matrix chart by using SQL Server database as the data source.

To start with it, I created a new Power BI file first and then executed the SQL script in SQL Server.

Once the query is executed in SQL server, come back to Power BI desktop and connect Power BI to SQL Server by using Get data option.

There are multiple tables loaded but I need only selective tables to be added in Power BI to create report.

So I chose four tables and loaded 

• tblCentre
• tblPurchase
• tblRegion
• tblTown        

Now all the four tables with their variables are shown under field pane. 

To create the required Matrix Chart, I selected the chart from Visualization pane and a blank Matrix was displayed on canvas.

RegionName from Region table was added under Rows section, Quantity from Purchase table was added to values and PurchaseDate from Purchase table was added under Column section.

Since Quantity was summarized to SUM in values and I needed the average so made changed SUM to AVERAGE.

The chart is formed, now the formatting part is required.

Changed the font size and background color of Column headers and Row headers under Format pane.

Also added title to the Matrix named as Quantity sold by Quarter.

The year in Row header was drilled down to Quarter to get deep analysis.

Saving the file by name 'That was easy.pbix'.