SUMX and iterators in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand the total revenue generated by each product category, considering the quantity sold and unit price for each product.

📊 Data: You have a sales table with columns: ProductCategory, ProductName, QuantitySold, and UnitPrice.

🎯 Deliverable: Create a report that shows total revenue by product category using a calculated measure with SUMX.

Sample Data

ProductCategory	ProductName	QuantitySold	UnitPrice
Electronics	Smartphone	10	300
Electronics	Laptop	5	800
Home Appliances	Microwave	7	150
Home Appliances	Refrigerator	3	1200
Clothing	T-Shirt	20	15
Clothing	Jeans	10	40
Electronics	Headphones	15	50
Home Appliances	Blender	8	100

1

Step 1: Create a new calculated measure named 'Total Revenue' in Power BI.

Total Revenue = SUMX(Sales, Sales[QuantitySold] * Sales[UnitPrice])

Expected Result

The measure calculates total revenue for all sales rows by multiplying quantity and price, then summing.

2

Step 2: Create a table visual in Power BI with 'ProductCategory' as rows and the 'Total Revenue' measure as values.

Table visual configuration: Rows = ProductCategory, Values = Total Revenue

Expected Result

The table shows total revenue for each product category.

3

Step 3: Verify the total revenue values by manually calculating for one category, e.g., Electronics.

Electronics revenue = (10*300) + (5*800) + (15*50) = 3000 + 4000 + 750 = 7750

Expected Result

The Electronics category shows a total revenue of 7750.

Final Result

ProductCategory | Total Revenue
----------------|--------------
Electronics     | 7750
Home Appliances | 5450
Clothing        | 700

✓Electronics category generates the highest revenue of 7750.

✓Home Appliances follow with 5450 total revenue.

✓Clothing category has the lowest revenue of 700.

Bonus Challenge

Create a measure that calculates total revenue only for products where QuantitySold is greater than 5.

Show Hint

Use SUMX with a FILTER inside it: SUMX(FILTER(Sales, Sales[QuantitySold] > 5), Sales[QuantitySold] * Sales[UnitPrice])