Table and matrix layouts in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ visualization

intermediate

Identify the correct layout for showing hierarchical data

You want to display sales data grouped by Region, then by Country, and finally by City. Which visual layout is best suited for this hierarchical display?

A A Pie chart showing sales distribution by City

B A Table visual with Region, Country, and City as separate columns

C A Card visual showing total sales for each City

D A Matrix visual with Region as rows, Country as sub-rows, and City as sub-sub-rows

💡 Hint

Think about which visual allows expanding and collapsing hierarchical levels.

A Matrix visual supports hierarchical rows and allows users to drill down or expand levels, making it ideal for showing Region > Country > City.

❓ dax_lod_result

intermediate

Calculate total sales ignoring filters on Product Category

Given a sales table with columns: SalesAmount, ProductCategory, and Date, which DAX measure returns total sales ignoring any filter on ProductCategory but respects other filters?

Power BI

Total Sales Ignore Category = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[ProductCategory]))

A CALCULATE(SUM(Sales[SalesAmount]), REMOVEFILTERS(Sales[ProductCategory]))

B CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[ProductCategory]))

C SUM(Sales[SalesAmount])

D CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

💡 Hint

Use a function that removes filters only on ProductCategory.

ALL(Sales[ProductCategory]) removes filters on ProductCategory only, so the measure sums all sales ignoring that filter but respects others.

❓ data_modeling

advanced

Optimize a matrix visual with many columns for performance

You have a matrix visual showing monthly sales for 50 products over 3 years. The visual is slow to load. What is the best way to improve performance?

A Use a calculated column to pre-calculate monthly sales per product

B Add all products as columns instead of rows to reduce visual complexity

C Use a measure with SUM and filter context instead of calculated columns

D Remove the date filter to reduce the number of months shown

💡 Hint

Measures are generally more efficient than calculated columns for aggregations.

Using measures leverages DAX engine optimizations and reduces data model size, improving matrix visual performance.

🔧 Formula Fix

advanced

Identify the error causing incorrect subtotal in matrix visual

A matrix visual shows sales by Region and Product. The subtotal for Region shows zero instead of the sum. Which DAX measure causes this issue?

Sales Amount = IF(HASONEVALUE(Product[ProductName]), SUM(Sales[Amount]), 0)

Power BI

Sales Amount = IF(HASONEVALUE(Product[ProductName]), SUM(Sales[Amount]), 0)

A The measure returns 0 for subtotals because HASONEVALUE is false for multiple products

B The measure syntax is invalid and causes a syntax error

C The measure sums sales incorrectly because it uses SUM instead of SUMX

D The measure ignores filters on Region causing zero subtotal

💡 Hint

Check what HASONEVALUE returns when multiple products are in context.

HASONEVALUE returns false for subtotal rows with multiple products, so the measure returns 0 instead of sum, causing zero subtotal.

🧠 Conceptual

expert

Best practice for accessibility in matrix visuals

Which practice improves accessibility for users navigating a matrix visual with keyboard and screen readers?

A Use clear and descriptive column and row headers with ARIA labels

B Disable keyboard navigation to simplify interaction

C Use color only to indicate important data differences

D Hide all subtotals to reduce visual clutter

💡 Hint

Think about how screen readers identify table structure.

Descriptive headers and ARIA labels help screen readers understand the matrix layout, improving accessibility.

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