Bird
Raised Fist0
Power BIbi_tool~15 mins

Table and matrix visuals in Power BI - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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.
Progress0 / 8 steps
Sample Data
DateRegionProduct CategoryProduct NameUnits SoldSales Amount
2024-01-05NorthElectronicsSmartphone105000
2024-01-07SouthElectronicsLaptop57000
2024-01-10EastHome AppliancesMicrowave81600
2024-01-12WestHome AppliancesVacuum Cleaner61200
2024-01-15NorthFurnitureOffice Chair72100
2024-01-18SouthFurnitureDesk31500
2024-01-20EastElectronicsTablet92700
2024-01-22WestElectronicsSmartwatch4800
2024-01-25NorthHome AppliancesBlender5500
2024-01-28SouthHome AppliancesToaster6600
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.

Practice

(1/5)
1. What is the main difference between a Table visual and a Matrix visual in Power BI?
easy
A. Tables can show images, matrices cannot.
B. Matrix visuals allow grouping and subtotals, tables do not.
C. Tables support drill-down, matrices do not.
D. Matrix visuals cannot display numeric data.

Solution

  1. Step 1: Understand Table visual features

    Tables display data in simple rows and columns without grouping or subtotals.

  2. Step 2: Understand Matrix visual features

    Matrices allow grouping by rows and columns and show subtotals for those groups.

  3. Final Answer:

    Matrix visuals allow grouping and subtotals, tables do not. -> Option B

  4. Quick Check:

    Grouping and subtotals = Matrix [OK]
Hint: Remember: Matrix = grouping + subtotals, Table = simple rows [OK]
Common Mistakes:
  • Thinking tables support grouping and subtotals
  • Confusing drill-down features between visuals
  • Assuming matrices cannot show numbers
2. Which of the following is the correct way to add a field to the Rows area in a Matrix visual in Power BI?
easy
A. Drag the field into the Rows area.
B. Drag the field into the Filters area.
C. Drag the field into the Values area.
D. Drag the field into the Columns area.

Solution

  1. Step 1: Identify the Rows area in Matrix visual

    The Rows area is where you place fields to group data vertically in a matrix.

  2. Step 2: Correct placement of fields

    Dragging a field into the Rows area groups data by that field in rows.

  3. Final Answer:

    Drag the field into the Rows area. -> Option A

  4. Quick Check:

    Rows area = drag fields here for row grouping [OK]
Hint: Drag fields to Rows area for vertical grouping in matrix [OK]
Common Mistakes:
  • Dragging fields to Values instead of Rows
  • Confusing Columns and Rows areas
  • Placing fields in Filters instead of Rows
3. Given a Matrix visual with Category in Rows and Year in Columns, what will be shown if you add Sales to Values?
medium
A. A list of all sales transactions without grouping.
B. Sales totals only by Category without Year breakdown.
C. Sales totals only by Year without Category grouping.
D. Sales totals grouped by Category and Year with subtotals.

Solution

  1. Step 1: Understand Matrix grouping by Rows and Columns

    Category in Rows groups data vertically; Year in Columns groups data horizontally.

  2. Step 2: Adding Sales to Values

    Sales values will be aggregated for each Category-Year combination, showing subtotals.

  3. Final Answer:

    Sales totals grouped by Category and Year with subtotals. -> Option D

  4. Quick Check:

    Matrix with Rows + Columns = grouped totals [OK]
Hint: Rows + Columns + Values = grouped totals with subtotals [OK]
Common Mistakes:
  • Ignoring the column grouping by Year
  • Expecting ungrouped transaction list
  • Confusing subtotals with raw data
4. You created a Matrix visual but subtotals are not showing. What is the most likely reason?
medium
A. Subtotals are disabled in the Matrix visual settings.
B. You did not add any fields to the Values area.
C. The data source has no numeric columns.
D. Matrix visuals never show subtotals.

Solution

  1. Step 1: Check Matrix visual subtotal settings

    Subtotals can be turned on or off in the Format pane under Subtotals.

  2. Step 2: Confirm fields and data type

    Even with fields added, subtotals won't show if disabled in settings.

  3. Final Answer:

    Subtotals are disabled in the Matrix visual settings. -> Option A

  4. Quick Check:

    Subtotals off in settings = no subtotals shown [OK]
Hint: Check subtotal toggle in Format pane if subtotals missing [OK]
Common Mistakes:
  • Assuming subtotals show automatically without enabling
  • Thinking numeric data is required for subtotals
  • Believing matrices never show subtotals
5. You want to create a report showing sales by Product Category and Region with subtotals for each. Which visual and setup is best?
hard
A. Use a Table visual with Product Category and Region in Values, Sales in Filters.
B. Use a Matrix visual with Sales in Rows, Product Category in Values, Region in Filters.
C. Use a Matrix visual with Product Category in Rows, Region in Columns, and Sales in Values.
D. Use a Table visual with Region in Rows and Sales in Columns.

Solution

  1. Step 1: Identify need for grouping and subtotals

    Showing sales by Product Category and Region with subtotals requires grouping on both dimensions.

  2. Step 2: Choose visual and field placement

    Matrix visual supports grouping by Rows and Columns and shows subtotals; place Product Category in Rows, Region in Columns, Sales in Values.

  3. Final Answer:

    Use a Matrix visual with Product Category in Rows, Region in Columns, and Sales in Values. -> Option C

  4. Quick Check:

    Matrix + Rows + Columns + Values = grouped subtotals [OK]
Hint: Matrix with Rows and Columns fields shows subtotals best [OK]
Common Mistakes:
  • Using Table visual which lacks subtotals
  • Placing Sales in Filters or wrong areas
  • Confusing Rows and Values placement
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard