Table and matrix visuals in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add a column to a table visual in Power BI.

Power BI

Table.AddColumn(Source, "NewColumn", each [1])

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

A[Sales] * 1.1

B[Date]

C[Region]

D[Product]

Attempts:

3 left

💡 Hint

Common Mistakes

Using column names without 'each' keyword

Using incorrect syntax for multiplication

To add a calculated column showing 10% more sales, use each [Sales] * 1.1.

2fill in blank

medium

Complete the DAX formula to create a measure that sums the Sales column.

Power BI

Total Sales = [1](Sales[Amount])

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

ACOUNT

BAVERAGE

CSUM

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVERAGE instead of SUM

Using COUNT which counts rows, not sums values

The SUM function adds all values in the Sales Amount column.

3fill in blank

hard

Fix the error in the DAX formula to calculate average sales per region.

Power BI

Average Sales = CALCULATE(AVERAGE(Sales[Amount]), [1](Sales[Region]))

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

AFILTER

BVALUES

CALL

DSUMMARIZE

Attempts:

3 left

💡 Hint

Common Mistakes

Using FILTER which requires a table expression

Using ALL which removes filters instead of applying them

The VALUES function returns the distinct regions to calculate average per region.

4fill in blank

hard

Fill both blanks to create a matrix visual grouping sales by Region and Product.

Power BI

MatrixVisual.Rows = [1]
MatrixVisual.Columns = [2]

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

ASales[Region]

BSales[Amount]

CSales[Product]

DSales[Date]

Attempts:

3 left

💡 Hint

Common Mistakes

Using Amount as a grouping field

Using Date instead of Product for columns

Rows should be grouped by Region and columns by Product for the matrix visual.

5fill in blank

hard

Fill all three blanks to create a measure that calculates total sales for the current year.

Power BI

Total Sales CY = CALCULATE([1](Sales[Amount]),
    [2](Sales[Date].[Year], Sales[Date].[Year] = YEAR(TODAY())),
    [3](Sales))

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

ASUM

BFILTER

CALL

DAVERAGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVERAGE instead of SUM

Not using FILTER for the year condition

Not using ALL to clear filters

Use SUM to add sales, FILTER to filter current year, and ALL to remove other filters.

Practice

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