Table and matrix layouts in Power BI - Dashboard Guide

Dashboard Mode - Table and matrix layouts

Dashboard Goal

Understand sales performance by product and region using tables and matrix layouts to compare values clearly.

Sample Data

Region	Product	Sales	Quantity
North	Apples	100	50
North	Bananas	150	75
South	Apples	200	100
South	Bananas	120	60
East	Apples	180	90
East	Bananas	160	80
West	Apples	140	70
West	Bananas	130	65

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUM(Sales[Sales])
  Result: 1180
• Table: Sales by Region and Product
  Columns: Region, Product, Sales, Quantity
  Shows raw data rows for detailed view.
• Matrix: Sales Summary Matrix
  Rows: Region
  Columns: Product
  Values: Sum of Sales
  Formula for values: SUM(Sales[Sales])
  Shows sales totals by region and product in a grid layout for easy comparison.

Dashboard Layout

+----------------------+----------------------+
|      Total Sales      |                      |
|        1180          |                      |
+----------------------+    Sales by Region    |
|                      |    and Product Table  |
|                      |                      |
|                      |                      |
+----------------------+----------------------+
|                      Sales Summary Matrix   |
|                                              |
|                                              |
+----------------------------------------------+

Interactivity

A slicer for Region filters both the Table and Matrix components. Selecting a region updates the sales data shown in the table and the matrix to only that region's data. The Total Sales KPI card also updates to show the sum of sales for the selected region.

Self Check

If you add a filter for Region = South, which components update and what changes occur?

• Total Sales KPI: Updates to show 320 (200 + 120).
• Sales by Region and Product Table: Shows only rows where Region is South.
• Sales Summary Matrix: Shows sales only for South region with Apples = 200 and Bananas = 120.

Key Result

Dashboard shows total sales, detailed sales table, and sales summary matrix by region and product with interactive region filter.

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