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.