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.

Practice

1. What is the main difference between a Table and a Matrix visual in Power BI?

easy

A. A Table can only show numbers, but a Matrix can show text and numbers.

B. A Table shows data in rows and columns without grouping, while a Matrix can group data and show subtotals.

C. A Matrix cannot display totals, but a Table can.

D. A Table automatically creates charts, but a Matrix does not.

Solution

1. Step 1: Understand Table layout

   A Table visual displays data simply in rows and columns without grouping or subtotals.

2. Step 2: Understand Matrix layout

   A Matrix allows grouping by rows and columns and can show subtotals and drill-downs.

3. Final Answer:

   A Table shows data in rows and columns without grouping, while a Matrix can group data and show subtotals. -> Option B

4. Quick Check:

   Table vs Matrix grouping = A Table shows data in rows and columns without grouping, while a Matrix can group data and show subtotals. [OK]

Hint: Remember: Matrix groups data; Table just lists it [OK]

Common Mistakes:

• Thinking Tables can group data like Matrices
• Believing Matrices cannot show totals
• Confusing data types shown in Tables vs Matrices

2. Which of the following is the correct way to add a subtotal in a Matrix visual in Power BI?

easy

A. Use a slicer to filter subtotal values.

B. Add a calculated column with subtotal values in the data table.

C. Enable the 'Subtotals' option in the Format pane under 'Row headers'.

D. Subtotals are added automatically and cannot be controlled.

Solution

1. Step 1: Locate subtotal settings in Matrix

   In Power BI, subtotals for rows or columns are controlled in the Format pane under 'Row headers' or 'Column headers'.

2. Step 2: Enable subtotals option

   Turning on the 'Subtotals' toggle adds subtotal rows or columns in the Matrix visual.

3. Final Answer:

   Enable the 'Subtotals' option in the Format pane under 'Row headers'. -> Option C

4. Quick Check:

   Subtotal toggle in Format pane = Enable the 'Subtotals' option in the Format pane under 'Row headers'. [OK]

Hint: Subtotals toggle is in Format pane, not data model [OK]

Common Mistakes:

• Trying to add subtotals via calculated columns
• Assuming subtotals appear without enabling them
• Using slicers to control subtotals

3. Given a Matrix visual with 'Category' in Rows and 'Sales' in Values, what will happen if you add 'Region' to Columns?

medium

A. The Matrix will show Sales grouped by Category and Region with subtotals for each Region.

B. The Matrix will only show Sales by Region, ignoring Category.

C. The Matrix will show a flat list of Sales without grouping.

D. Adding Region to Columns will cause an error.

Solution

1. Step 1: Understand Matrix layout with Rows and Columns

   Rows group data vertically, Columns group data horizontally, creating a grid showing intersections.

2. Step 2: Adding Region to Columns groups Sales by Category (rows) and Region (columns)

   This creates a matrix showing Sales values for each Category-Region pair with subtotals per Region.

3. Final Answer:

   The Matrix will show Sales grouped by Category and Region with subtotals for each Region. -> Option A

4. Quick Check:

   Matrix grouping by Rows and Columns = The Matrix will show Sales grouped by Category and Region with subtotals for each Region. [OK]

Hint: Rows group vertically, Columns group horizontally in Matrix [OK]

Common Mistakes:

• Thinking adding columns removes row grouping
• Expecting flat list instead of grouped matrix
• Assuming error occurs when adding columns

4. You created a Matrix visual but subtotals are not showing. What is the most likely reason?

medium

A. Matrix visuals never show subtotals.

B. The data source does not have numeric values.

C. You added too many fields to the Rows area.

D. The 'Subtotals' option in the Format pane is turned off.

Solution

1. Step 1: Check Format pane settings for subtotals

   Subtotals must be enabled in the Format pane; if off, subtotals won't appear.

2. Step 2: Verify data type and fields

   Numeric values are needed for meaningful subtotals, but subtotals can still show if enabled.

3. Final Answer:

   The 'Subtotals' option in the Format pane is turned off. -> Option D

4. Quick Check:

   Subtotals toggle off = missing subtotals [OK]

Hint: Check Format pane subtotals toggle first [OK]

Common Mistakes:

• Assuming subtotals show automatically
• Blaming data type without checking settings
• Thinking Matrix can't show subtotals

5. You want to create a report showing monthly sales by product category with subtotals for each category and a grand total. Which layout and settings should you use?

hard

A. Use a Matrix visual with 'Month' in Columns, 'Category' in Rows, and enable subtotals for rows and columns.

B. Use a Table visual with 'Month' and 'Category' in columns and enable subtotals in the data model.

C. Use a Matrix visual with 'Category' in Columns, 'Month' in Rows, and disable subtotals.

D. Use a Table visual with only 'Sales' values and add subtotals manually.

Solution

1. Step 1: Choose Matrix for grouping and subtotals

   Matrix supports grouping by rows and columns and shows subtotals and grand totals.

2. Step 2: Place 'Month' in Columns and 'Category' in Rows

   This layout shows months horizontally and categories vertically, making subtotals clear.

3. Step 3: Enable subtotals for rows and columns

   Turn on subtotals in Format pane to show category subtotals and grand totals.

4. Final Answer:

   Use a Matrix visual with 'Month' in Columns, 'Category' in Rows, and enable subtotals for rows and columns. -> Option A

5. Quick Check:

   Matrix with grouping and subtotals = Use a Matrix visual with 'Month' in Columns, 'Category' in Rows, and enable subtotals for rows and columns. [OK]

Hint: Matrix + grouping + subtotals = best for grouped summaries [OK]

Common Mistakes:

• Using Table when subtotals needed
• Disabling subtotals accidentally
• Swapping rows and columns causing confusing layout