Calculated columns vs measures in Power BI - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a calculated column that adds 10 to the SalesAmount.

Power BI

NewColumn = Sales[SalesAmount] [1] 10

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

A*

B+

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '-' subtracts instead of adds.

Using '*' or '/' changes the value incorrectly.

The plus sign (+) adds 10 to each SalesAmount value in the calculated column.

2fill in blank

medium

Complete the measure to calculate total sales by summing SalesAmount.

Power BI

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

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

ASalesAmount

BPrice

CDiscount

DQuantity

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing Quantity sums units, not sales money.

Choosing Discount or Price does not sum total sales.

The SUM function adds all values in the SalesAmount column to get total sales.

3fill in blank

hard

Fix the error in this calculated column that tries to multiply Quantity by Price.

Power BI

TotalValue = Sales[Quantity] [1] Sales[Price]

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

A-

B+

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' adds values instead of multiplying.

Using '-' or '/' changes the calculation incorrectly.

The asterisk (*) multiplies Quantity by Price to get total value per row.

4fill in blank

hard

Fill both blanks to create a measure that calculates average sales per order.

Power BI

Average Sales = [1](Sales[SalesAmount]) [2] COUNT(Sales[OrderID])

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

ASUM

B/

C*

DAVERAGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVERAGE instead of SUM in numerator changes the calculation.

Using '*' instead of '/' changes the meaning.

This measure sums all sales amounts and divides by the count of orders to find average sales per order.

5fill in blank

hard

Fill all three blanks to create a calculated column that flags high sales over 1000.

Power BI

HighSalesFlag = IF(Sales[SalesAmount] [1] 1000, [2], [3])

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

A>

B1

C0

DSales[SalesAmount]

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' reverses the logic.

Returning SalesAmount instead of 1 or 0 confuses the flag.

This calculated column checks if SalesAmount is greater than 1000. If yes, it returns 1 (flag), else 0.

Practice

1. Which of the following best describes a calculated column in Power BI?

easy

A. A visual element used to display data on a report page.

B. A dynamic calculation that changes based on filters and user selections.

C. A new column added to a table with values calculated row by row and stored in the model.

D. A type of filter applied to slicers in reports.

Solution

1. Step 1: Understand what a calculated column is

   A calculated column is created by adding a new column to a table where each row's value is computed and stored permanently.

2. Step 2: Differentiate from measures

   Measures are dynamic and calculate results on the fly based on filters, unlike calculated columns which are static.

3. Final Answer:

   A new column added to a table with values calculated row by row and stored in the model. -> Option C

4. Quick Check:

   Calculated column = static row-level data [OK]

Hint: Calculated columns store data row-wise; measures calculate dynamically [OK]

Common Mistakes:

• Confusing calculated columns with measures
• Thinking calculated columns change with filters
• Believing measures create new table columns

2. Which of the following is the correct syntax to create a row-by-row calculated column in Power BI using DAX?

easy

A. NewColumn = SUM(Sales[Amount])

B. NewColumn = Sales[Quantity] * Sales[Price]

C. Measure = CALCULATE(SUM(Sales[Amount]))

D. NewColumn := Sales[Quantity] + Sales[Discount]

Solution

1. Step 1: Identify calculated column syntax

   Calculated columns use simple assignment with = and row context expressions like Sales[Quantity] * Sales[Price].

2. Step 2: Check options for correct syntax

   NewColumn = Sales[Quantity] * Sales[Price] uses correct syntax and row-level calculation. NewColumn = SUM(Sales[Amount]) uses aggregation which is for measures. Measure = CALCULATE(SUM(Sales[Amount])) defines a measure, not a column. NewColumn := Sales[Quantity] + Sales[Discount] uses := which is invalid in DAX.

3. Final Answer:

   NewColumn = Sales[Quantity] * Sales[Price] -> Option B

4. Quick Check:

   Calculated column syntax = simple row formula [OK]

Hint: Calculated columns use = with row-level expressions, not aggregation [OK]

Common Mistakes:

• Using aggregation functions in calculated columns
• Using := instead of = in DAX
• Confusing measure syntax with calculated column syntax

3. Given the following DAX measure:

Total Sales = SUM(Sales[Amount])

and a calculated column:

Discounted Price = Sales[Price] * 0.9

What will be the result of Total Sales if a report filter selects only products with Discounted Price less than 100?

medium

A. The sum of Sales[Amount] for all rows where Discounted Price is less than 100.

B. The sum of Sales[Amount] for all rows, ignoring the filter on Discounted Price.

C. An error because measures cannot use calculated columns in filters.

D. Zero, because Discounted Price is not a measure.

Solution

1. Step 1: Understand filter context on measures

   Measures calculate dynamically and respect report filters, including filters on calculated columns.

2. Step 2: Apply filter on Discounted Price

   The filter selects rows where Discounted Price < 100, so Total Sales sums only those rows' Amount values.

3. Final Answer:

   The sum of Sales[Amount] for all rows where Discounted Price is less than 100. -> Option A

4. Quick Check:

   Measures respect filters on calculated columns [OK]

Hint: Measures recalc with filters, including on calculated columns [OK]

Common Mistakes:

• Thinking measures ignore filters on calculated columns
• Believing calculated columns cannot be used in filters
• Assuming error occurs when filtering on calculated columns

4. You wrote this DAX measure:

Total Discount = SUM(Sales[Discounted Price])

but it returns an error. What is the most likely cause?

medium

A. Discounted Price is a calculated column, but the table Sales does not contain it.

B. Discounted Price is a measure, and measures cannot be summed directly.

C. Discounted Price is a calculated column, so SUM cannot be used on it.

D. Discounted Price is a calculated column but the column name is misspelled.

Solution

1. Step 1: Understand SUM usage on calculated columns

   SUM can be used on calculated columns if they exist in the table.

2. Step 2: Check for existence and spelling

   If the column does not exist or is misspelled, SUM will cause an error. The most likely cause is that the calculated column Discounted Price does not exist in the Sales table.

3. Final Answer:

   Discounted Price is a calculated column, but the table Sales does not contain it. -> Option A

4. Quick Check:

   SUM errors if column missing [OK]

Hint: Check column existence before summing [OK]

Common Mistakes:

• Assuming SUM cannot be used on calculated columns
• Confusing measures and columns in aggregation
• Ignoring spelling or table errors

5. You want to create a report showing the average sales per customer, but only for customers who bought more than 5 items. Which approach is best?

hard

A. Create a calculated column counting items per customer, then a measure averaging sales filtered by that column.

B. Create a measure summing sales and ignore item counts.

C. Create a calculated column averaging sales per customer and filter customers in the report.

D. Create a measure that calculates total items per customer and filters average sales dynamically.

Solution

1. Step 1: Understand dynamic filtering needs

   Filtering customers by items bought requires dynamic calculation because user filters may change.

2. Step 2: Choose measure for dynamic aggregation

   A measure can calculate total items per customer and filter average sales dynamically, adapting to report filters.

3. Step 3: Why not calculated columns?

   Calculated columns are static and cannot respond to slicers or filters dynamically, so Create a calculated column counting items per customer, then a measure averaging sales filtered by that column. is less flexible.

4. Final Answer:

   Create a measure that calculates total items per customer and filters average sales dynamically. -> Option D

5. Quick Check:

   Use measures for dynamic filtering and aggregation [OK]

Hint: Use measures for dynamic filters and aggregations [OK]

Common Mistakes:

• Using calculated columns for dynamic filters
• Ignoring filter context in measures
• Trying to average in calculated columns