Table and matrix layouts in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple table visual showing Sales Amount.

Power BI

Table = SUMMARIZE(Sales, Sales[Product], "TotalSales", [1])

Drag options to blanks, or click blank then click option'

ASUM(Sales[SalesAmount])

BCOUNT(Sales[SalesAmount])

CAVERAGE(Sales[SalesAmount])

DMIN(Sales[SalesAmount])

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT instead of SUM

Using AVERAGE which calculates mean, not total

Use SUM to calculate the total sales amount for each product in the table.

2fill in blank

medium

Complete the code to add a matrix visual grouping by Region and Product.

Power BI

Matrix = SUMMARIZE(Sales, Sales[Region], Sales[Product], "TotalSales", [1])

Drag options to blanks, or click blank then click option'

ACOUNT(Sales[SalesAmount])

BSUM(Sales[SalesAmount])

CMAX(Sales[SalesAmount])

DMIN(Sales[SalesAmount])

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT which counts rows, not sums values

Using MAX or MIN which find extremes, not totals

SUM aggregates sales amount by Region and Product for the matrix layout.

3fill in blank

hard

Fix the error in the measure to calculate total sales for the matrix.

Power BI

Total Sales = CALCULATE([1], ALL(Sales[Product]))

Drag options to blanks, or click blank then click option'

AAVERAGE(Sales[SalesAmount])

BCOUNT(Sales[SalesAmount])

CSUMX(Sales, Sales[SalesAmount])

DSUM(Sales[SalesAmount])

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT which counts rows

Using AVERAGE which calculates mean

Using SUMX unnecessarily

CALCULATE needs a proper aggregation like SUM to compute total sales ignoring product filter.

4fill in blank

hard

Fill both blanks to create a matrix showing sales by Year and Category with total sales.

Power BI

Matrix = SUMMARIZE(Sales, Sales[[1]], Sales[[2]], "TotalSales", SUM(Sales[SalesAmount]))

Drag options to blanks, or click blank then click option'

AYear

BCategory

CRegion

DProduct

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping Category and Year

Using Region or Product instead of Category

Year and Category are common matrix row and column groups for sales analysis.

5fill in blank

hard

Fill all three blanks to create a measure that calculates average sales per product in a matrix.

Power BI

Avg Sales = DIVIDE(SUM(Sales[[1]]), COUNT(Sales[[2]]), [3])

Drag options to blanks, or click blank then click option'

ASalesAmount

BProductID

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using ProductID in SUM

Using 1 instead of 0 as alternate result

Swapping SUM and COUNT columns

Sum sales amount divided by count of products, with 0 as alternate result if division by zero.

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