Table and matrix layouts in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

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

A Table shows data in a simple grid with rows and columns, while a Matrix allows grouping and nesting of rows and columns, similar to a pivot table.

intermediate

How can you add subtotals in a Matrix visual?

You can enable subtotals in the Format pane under Subtotals, which will show summary values for each group in rows or columns.

beginner

Why would you use a Matrix visual instead of a Table visual?

Use a Matrix when you want to show hierarchical data with expandable groups or when you want to display data summarized by multiple categories.

intermediate

What feature in Matrix allows you to drill down into data?

The drill down feature lets you click on a group to expand or collapse details, helping explore data at different levels.

intermediate

How do you freeze columns or rows in a Table visual for easier reading?

You can freeze the first column by enabling the 'Lock first column' option in the Format pane, so it stays visible when scrolling.

Which visual in Power BI supports hierarchical row and column grouping?

A Matrix

B Table

C Card

D Pie Chart

The Matrix visual supports hierarchical grouping in both rows and columns, unlike the Table visual.

How can you show totals for each group in a Matrix?

A Add a new measure

B Change the data source

C Use a slicer

D Enable subtotals in the Format pane

Subtotals can be turned on in the Format pane to show totals for groups in a Matrix.

What happens when you enable drill down in a Matrix visual?

A The visual exports to Excel

B The visual changes to a Table

C You can expand or collapse grouped data levels

D Data refreshes automatically

Drill down allows users to explore data by expanding or collapsing groups in the Matrix.

Which feature helps keep important columns visible when scrolling in a Table visual?

A Data labels

B Lock first column

C Conditional formatting

D Bookmarks

Locking the first column keeps it visible while scrolling horizontally.

Can you add multiple measures as columns in a Matrix visual?

A Yes, multiple measures can be added as columns

B No, only one measure is allowed

C Only if measures are numeric

D Only if measures are text

Matrix visuals support multiple measures as columns to compare values side by side.

Explain how a Matrix visual can help analyze sales data by region and product category.

Think about grouping rows by region and columns by product category with totals.

Describe the steps to format a Table visual to freeze the first column and add alternating row colors.

Look in the Format settings for these options.

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