SUMX and iterators in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate total sales with SUMX iterator

Given a table Sales with columns Quantity and UnitPrice, what is the result of this DAX measure?

Measure = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

Assume the table has these rows:

• Quantity: 2, UnitPrice: 10
• Quantity: 3, UnitPrice: 15
• Quantity: 1, UnitPrice: 20

Power BI

Measure = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

A 30

B 45

C 85

D 50

💡 Hint

Multiply quantity by unit price for each row, then add all results.

Row 1: 2*10=20, Row 2: 3*15=45, Row 3: 1*20=20; Total = 20+45+20 = 85.

❓ visualization

intermediate

Best visualization for SUMX sales by category

You have a measure using SUMX to calculate total sales per product category. Which visualization best shows the sales distribution across categories?

A Pie chart showing sales percentage per category

B Line chart showing sales over time

C Scatter plot of sales vs. quantity

D Table listing product names and sales

💡 Hint

Think about showing parts of a whole for categories.

A pie chart clearly shows how total sales are divided among categories, matching the SUMX measure per category.

🔧 Formula Fix

advanced

Identify error in SUMX measure with filter

What error occurs with this DAX measure?

Measure = SUMX(FILTER(Sales, Sales[Quantity] > 2), Sales[Quantity] * Sales[UnitPrice])

Assume Sales table has rows with Quantity values 1, 2, 3.

Power BI

Measure = SUMX(FILTER(Sales, Sales[Quantity] > 2), Sales[Quantity] * Sales[UnitPrice])

A Returns the sum of Quantity * UnitPrice for rows where Quantity > 2

B Returns 0 because no rows match the filter

C Syntax error due to incorrect FILTER usage

D Runtime error because Sales table is not referenced correctly

💡 Hint

FILTER returns rows where Quantity is greater than 2.

FILTER returns rows with Quantity > 2 (only Quantity=3), SUMX multiplies and sums those rows correctly.

❓ data_modeling

advanced

Using SUMX with related tables

You have two tables: Orders with columns OrderID, CustomerID, and Quantity, and Products with columns ProductID, CustomerID, and UnitPrice. You want to calculate total sales per customer using SUMX. Which DAX measure correctly calculates this?

A Total Sales = SUMX(Orders, Orders[Quantity] + RELATED(Products[UnitPrice]))

B Total Sales = SUMX(Orders, Orders[Quantity] * RELATED(Products[UnitPrice]))

C Total Sales = SUMX(Orders, Orders[Quantity] * Products[UnitPrice])

D Total Sales = SUMX(Products, Products[UnitPrice] * RELATED(Orders[Quantity]))

💡 Hint

Use RELATED to get unit price from Products for each order row.

Option B correctly multiplies quantity by unit price using RELATED to access Products data per order row.

🧠 Conceptual

expert

Why use SUMX instead of SUM for calculated columns?

Which statement best explains why SUMX is preferred over SUM when calculating total sales as Quantity * UnitPrice?

A SUM automatically multiplies columns before summing, SUMX does not.

B SUMX is faster than SUM because it uses iterators internally.

C SUMX can only be used with calculated columns, SUM cannot.

D SUMX iterates row by row allowing multiplication before summing, while SUM only sums a single column without calculation.

💡 Hint

Think about how calculations happen per row.

SUMX iterates each row to calculate Quantity * UnitPrice before summing, SUM only sums one column's values without row-wise calculation.

Practice

1. What does the SUMX function do in Power BI?

easy

A. It sums values after calculating an expression for each row in a table.

B. It counts the number of rows in a table.

C. It filters rows based on a condition.

D. It returns the maximum value from a column.

Solution

1. Step 1: Understand the purpose of SUMX

   SUMX iterates over each row of a table and evaluates an expression for that row.

2. Step 2: Summation after calculation

   It then sums all the results of these row-wise calculations to produce a total.

3. Final Answer:

   It sums values after calculating an expression for each row in a table. -> Option A

4. Quick Check:

   SUMX = sum after row calculation [OK]

Hint: Remember: SUMX sums after row-by-row math [OK]

Common Mistakes:

• Confusing SUMX with SUM which sums directly
• Thinking SUMX counts rows
• Assuming SUMX filters data

2. Which of the following is the correct syntax for using SUMX in DAX?

easy

A. SUMX(Table, Expression)

B. SUMX(Expression, Table)

C. SUMX(Table)

D. SUMX(Expression)

Solution

1. Step 1: Identify SUMX parameters

   SUMX requires two parameters: a table to iterate over and an expression to evaluate per row.

2. Step 2: Confirm correct order

   The correct order is SUMX(Table, Expression), where Table is first, then Expression.

3. Final Answer:

   SUMX(Table, Expression) -> Option A

4. Quick Check:

   SUMX syntax = SUMX(Table, Expression) [OK]

Hint: Table first, expression second in SUMX [OK]

Common Mistakes:

• Swapping the order of parameters
• Providing only one argument
• Using incorrect parameter types

3. Given the table Sales with columns Quantity and Price, what is the result of this measure?

SUMX(Sales, Sales[Quantity] * Sales[Price])

medium

A. The maximum Quantity sold in any transaction

B. The total number of sales transactions

C. The average price of all sales

D. The sum of Quantity multiplied by Price for each row

Solution

1. Step 1: Understand the expression inside SUMX

   For each row, it multiplies Quantity by Price, calculating the sales amount per transaction.

2. Step 2: SUMX sums all these calculated amounts

   It adds all these row-wise sales amounts to get the total sales value.

3. Final Answer:

   The sum of Quantity multiplied by Price for each row -> Option D

4. Quick Check:

   SUMX = sum of (Quantity * Price) [OK]

Hint: SUMX sums after multiplying per row [OK]

Common Mistakes:

• Thinking it counts rows instead of summing
• Confusing sum with average or max
• Ignoring the multiplication inside SUMX

4. Identify the error in this DAX formula:

SUMX(Sales, Sales[Quantity] + )

medium

A. Table name is incorrect

B. Missing expression after the plus sign

C. SUMX cannot use addition inside expression

D. Quantity column does not exist

Solution

1. Step 1: Check the expression inside SUMX

   The expression ends with a plus sign but has no value after it, making it incomplete.

2. Step 2: Understand syntax requirements

   Every operator must have operands on both sides; missing operand causes syntax error.

3. Final Answer:

   Missing expression after the plus sign -> Option B

4. Quick Check:

   Incomplete expression causes syntax error [OK]

Hint: Check for incomplete expressions after operators [OK]

Common Mistakes:

• Ignoring incomplete expressions
• Assuming SUMX disallows addition
• Mistaking table or column names

5. You want to calculate total profit where profit per sale is (Price - Cost) * Quantity. Which DAX formula using SUMX correctly calculates this?

hard

A. SUMX(Sales, Sales[Price] - Sales[Cost] * Sales[Quantity])

B. SUMX(Sales, Sales[Price] - (Sales[Cost] * Sales[Quantity]))

C. SUMX(Sales, (Sales[Price] - Sales[Cost]) * Sales[Quantity])

D. SUMX(Sales, Sales[Price] * Sales[Cost] * Sales[Quantity])

Solution

1. Step 1: Understand the profit formula per row

   Profit per sale is (Price - Cost) multiplied by Quantity, so parentheses are needed to subtract before multiplying.

2. Step 2: Check each option's expression

   SUMX(Sales, (Sales[Price] - Sales[Cost]) * Sales[Quantity]) correctly uses parentheses to subtract Price and Cost first, then multiplies by Quantity.

3. Final Answer:

   SUMX(Sales, (Sales[Price] - Sales[Cost]) * Sales[Quantity]) -> Option C

4. Quick Check:

   Correct order of operations with parentheses [OK]

Hint: Use parentheses to ensure correct math order [OK]

Common Mistakes:

• Missing parentheses causing wrong calculation
• Multiplying Cost by Quantity before subtracting
• Using addition instead of subtraction