Table and matrix visuals 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 showing sales details by product category and region, with totals and subtotals, to understand where sales are strongest.

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

🎯 Deliverable: Create a Power BI report page with a matrix visual showing sales amount by Region and Product Category, including subtotals and grand totals, and a table visual listing detailed sales records.

Sample Data

Date	Region	Product Category	Product Name	Units Sold	Sales Amount
2024-01-05	North	Electronics	Smartphone	10	5000
2024-01-07	South	Electronics	Laptop	5	7000
2024-01-10	East	Home Appliances	Microwave	8	1600
2024-01-12	West	Home Appliances	Vacuum Cleaner	6	1200
2024-01-15	North	Furniture	Office Chair	7	2100
2024-01-18	South	Furniture	Desk	3	1500
2024-01-20	East	Electronics	Tablet	9	2700
2024-01-22	West	Electronics	Smartwatch	4	800
2024-01-25	North	Home Appliances	Blender	5	500
2024-01-28	South	Home Appliances	Toaster	6	600

1

Step 1: Load the sales data into Power BI Desktop from your data source.

Use 'Get Data' > select your data source > load the table with columns Date, Region, Product Category, Product Name, Units Sold, Sales Amount.

Expected Result

Sales data is loaded and visible in the Fields pane.

2

Step 2: Create a new measure to calculate total sales amount.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

A measure named 'Total Sales' appears in the Fields pane and sums sales amounts.

3

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

From Visualizations pane, select 'Matrix'.

Expected Result

A blank matrix visual appears on the report page.

4

Step 4: Add 'Region' to Rows, 'Product Category' to Columns, and 'Total Sales' measure to Values in the matrix.

Drag 'Region' to Rows, 'Product Category' to Columns, 'Total Sales' to Values.

Expected Result

Matrix shows sales amount by Region and Product Category with subtotals and grand totals.

5

Step 5: Format the matrix to show subtotals and grand totals clearly.

In Format pane > Subtotals > turn on Row subtotals and Column subtotals; > Grand totals > turn on for Rows and Columns.

Expected Result

Matrix displays subtotals for each Region and Product Category and grand totals.

6

Step 6: Insert a Table visual below the matrix to show detailed sales records.

From Visualizations pane, select 'Table'.

Expected Result

A blank table visual appears on the report page.

7

Step 7: Add columns Date, Region, Product Category, Product Name, Units Sold, and Sales Amount to the table.

Drag these fields into the Values area of the table visual.

Expected Result

Table shows detailed sales records with all columns.

8

Step 8: Sort the table by Date ascending for easy reading.

Click the Date column header in the table visual to sort ascending.

Expected Result

Table rows are ordered by Date from earliest to latest.

Final Result

Matrix Visual (Sales Amount)
+---------+-------------------+-------------------+-------------------+------------+
| Region  | Electronics       | Home Appliances   | Furniture         | Grand Total|
+---------+-------------------+-------------------+-------------------+------------+
| North   | 5000              | 500               | 2100              | 7600       |
| South   | 7000              | 600               | 1500              | 9100       |
| East    | 2700              | 1600              |                   | 4300       |
| West    | 800               | 1200              |                   | 2000       |
+---------+-------------------+-------------------+-------------------+------------+
| Grand Total | 15500          | 3900              | 3600              | 23000      |

Table Visual (Detailed Sales Records)
+------------+--------+-----------------+----------------+------------+--------------+
| Date       | Region | Product Category| Product Name   | Units Sold | Sales Amount |
+------------+--------+-----------------+----------------+------------+--------------+
| 2024-01-05 | North  | Electronics     | Smartphone     | 10         | 5000         |
| 2024-01-07 | South  | Electronics     | Laptop         | 5          | 7000         |
| 2024-01-10 | East   | Home Appliances | Microwave      | 8          | 1600         |
| 2024-01-12 | West   | Home Appliances | Vacuum Cleaner | 6          | 1200         |
| 2024-01-15 | North  | Furniture       | Office Chair   | 7          | 2100         |
| 2024-01-18 | South  | Furniture       | Desk           | 3          | 1500         |
| 2024-01-20 | East   | Electronics     | Tablet         | 9          | 2700         |
| 2024-01-22 | West   | Electronics     | Smartwatch     | 4          | 800          |
| 2024-01-25 | North  | Home Appliances | Blender        | 5          | 500          |
| 2024-01-28 | South  | Home Appliances | Toaster        | 6          | 600          |
+------------+--------+-----------------+----------------+------------+--------------+

✓The South region has the highest total sales amount at 9,100.

✓Electronics is the top-selling product category across all regions with 15,500 in sales.

✓Home Appliances have lower sales but are consistent across regions.

✓The matrix visual helps quickly compare sales by region and category with clear subtotals.

✓The detailed table allows drilling down to individual sales transactions.

Bonus Challenge

Add conditional formatting to the matrix visual to highlight sales amounts above 5,000 in green and below 1,000 in red.

Show Hint

Use the Format pane > Conditional formatting > Background color or Font color on the 'Total Sales' values in the matrix.