Table and matrix layouts in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that shows total sales by product category and region, with the ability to see sales broken down by month within each region.

📊 Data: You have sales data including Date, Region, Product Category, and Sales Amount.

🎯 Deliverable: Create a Power BI report with a matrix visual that shows Product Categories as rows, Regions as columns, and monthly sales as nested columns inside each region.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-15	North	Electronics	1200
2024-01-20	South	Clothing	800
2024-02-05	North	Clothing	950
2024-02-10	East	Electronics	1100
2024-03-12	West	Furniture	1500
2024-03-15	South	Furniture	1300
2024-04-01	East	Clothing	700
2024-04-18	North	Furniture	1600
2024-05-05	West	Electronics	1400
2024-05-20	South	Electronics	900

1

Step 1: Load the sales data into Power BI Desktop by importing the data table.

Use 'Get Data' > 'Excel' or 'CSV' depending on your file format, then load the table.

Expected Result

Sales data table is loaded and visible in the Fields pane.

2

Step 2: Create a new column to extract the month name from the Date column for easier grouping.

New Column formula: Month = FORMAT('Sales'[Date], "MMMM")

Expected Result

A new column 'Month' appears showing month names like January, February, etc.

3

Step 3: Insert a Matrix visual onto the report canvas.

From Visualizations pane, select 'Matrix'.

Expected Result

An empty matrix visual appears on the report canvas.

4

Step 4: Add 'Product Category' to Rows in the matrix.

Drag 'Product Category' field to Rows area of the matrix visual.

Expected Result

Matrix rows show each product category like Electronics, Clothing, Furniture.

5

Step 5: Add 'Region' to Columns in the matrix.

Drag 'Region' field to Columns area of the matrix visual.

Expected Result

Matrix columns show each region: North, South, East, West.

6

Step 6: Add 'Month' to Columns below Region to create nested columns by month within each region.

Drag 'Month' field to Columns area below 'Region'.

Expected Result

Matrix columns now show regions with nested months inside each region.

7

Step 7: Add 'Sales Amount' to Values area to show total sales.

Drag 'Sales Amount' field to Values area; aggregation defaults to SUM.

Expected Result

Matrix shows sum of sales for each product category by region and month.

8

Step 8: Format the matrix for readability: enable word wrap on row headers and adjust column width.

In Format pane, turn on 'Word wrap' under Row headers and set column width to 'Auto'.

Expected Result

Matrix text is fully visible and columns adjust to content width.

Final Result

Matrix Visual Layout:

Rows: Product Category
Columns: Region > Month
Values: Sum of Sales Amount

+----------------+-----------------------------+
| Product        | North           South       |
| Category       | Jan Feb Mar Apr May Jan Feb |
+----------------+-----------------------------+
| Electronics    |1200  0   0   0 1400  0   900|
| Clothing       |  0  950  0    0    0  800  0 |
| Furniture      |  0    0 1500 1600  0 1300  0 |
+----------------+-----------------------------+

✓Electronics sales are strong in North and West regions, especially in May.

✓Clothing sales peak in South and North regions in January and February.

✓Furniture sales are highest in West and South regions in March and April.

Bonus Challenge

Add a slicer to the report to filter the matrix by year, so the manager can view sales for different years if data is updated.

Show Hint

Create a new column extracting the year from the Date column using YEAR('Sales'[Date]), then add a slicer visual with this Year field.