SUMX and iterators in Power BI - Dashboard Guide

Dashboard Mode - SUMX and iterators

Business Question

How much total revenue did each product category generate, considering the quantity sold and price per unit?

Sample Data

Product Category	Product	Quantity Sold	Price Per Unit
Electronics	Headphones	10	50
Electronics	Keyboard	5	80
Furniture	Chair	7	120
Furniture	Desk	3	200
Clothing	T-Shirt	20	15
Clothing	Jeans	10	40

Dashboard Components

• KPI Card: Total Revenue
  Formula: Total Revenue = SUMX(Sales, Sales[Quantity Sold] * Sales[Price Per Unit])
  Result: 10*50 + 5*80 + 7*120 + 3*200 + 20*15 + 10*40 = 500 + 400 + 840 + 600 + 300 + 400 = 3040
• Bar Chart: Revenue by Product Category
  Formula: Revenue by Category = SUMX(FILTER(Sales, Sales[Product Category] = EARLIER(Sales[Product Category])), Sales[Quantity Sold] * Sales[Price Per Unit])
  Values:
  • Electronics: (10*50)+(5*80) = 500 + 400 = 900
  • Furniture: (7*120)+(3*200) = 840 + 600 = 1440
  • Clothing: (20*15)+(10*40) = 300 + 400 = 700
• Table: Detailed Sales with Revenue
  Added Column Formula: Revenue = Sales[Quantity Sold] * Sales[Price Per Unit]
  Example Row: Headphones: 10 * 50 = 500

Dashboard Layout

+----------------------+-----------------------+
|      Total Revenue    |  Revenue by Category  |
|        (KPI Card)    |      (Bar Chart)      |
+----------------------+-----------------------+
|          Detailed Sales Table (with Revenue)          |
+-------------------------------------------------------+

Interactivity

A slicer for Product Category filters all components:

• KPI Card updates total revenue for the selected category.
• Bar Chart highlights or filters to the selected category.
• Detailed Sales Table shows only products from the selected category.

This helps focus analysis on one category at a time.

Self Check

If you add a filter for Product Category = Furniture, which components update and what are their new values?

• KPI Card: Total Revenue updates to 1440.
• Bar Chart: Shows only Furniture category bar with value 1440.
• Detailed Sales Table: Shows only Chair and Desk rows with their revenues.

Key Result

Dashboard shows total and category-wise revenue using SUMX to multiply quantity and price.

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