Microsoft Certified: Power BI Data Analyst AssociateModel the dataEasy
A client is developing a Power BI model to analyze product sales. The 'Sales' table includes 'ProductID', 'OrderQuantity', and 'UnitPrice'. The client needs a measure to calculate the total sales amount for all products. Which DAX expression should be used?
- ACALCULATE(SUM(Sales[OrderQuantity] * Sales[UnitPrice]))
- BAVERAGEX(Sales, Sales[OrderQuantity] * Sales[UnitPrice])
- CSUM(Sales[OrderQuantity] * Sales[UnitPrice])
- DSUMX(Sales, Sales[OrderQuantity] * Sales[UnitPrice])
Show answer & explanationAnswer & explanation
Correct answer: D. SUMX(Sales, Sales[OrderQuantity] * Sales[UnitPrice])
To calculate the total sales amount, you need to multiply 'OrderQuantity' by 'UnitPrice' for each row and then sum these individual row-level results. The SUMX function is an iterator that evaluates an expression for each row of a table and then aggregates the results, which is exactly what's needed here.
Why the other options are wrong
- A. CALCULATE() modifies context but doesn't solve the row-level calculation issue for SUM().
- B. AVERAGEX() would calculate the average sales per row, not the total sales amount.
- C. SUM() cannot perform row-by-row multiplication directly; it expects a single column as an argument.
SUMX for Row-Level Calculations
The SUMX function is an iterator that evaluates an expression for each row of a table, then sums the resulting values. It's crucial for calculations that involve multiple columns on a row-by-row basis before aggregation.
- Iterates over each row of a specified table.
- Evaluates an expression for each row.
- Aggregates the results (sums them up).
Memory trick: When you need to sum things 'X' times, use SUMX.