Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data analyst is developing a Power BI model for an e-commerce platform. The model includes a 'Sales' table with 'OrderID', 'ProductID', 'Quantity', and 'UnitPrice' columns. The analyst needs to calculate the 'Total Revenue' for each order item, which is 'Quantity' multiplied by 'UnitPrice'. Which DAX function is suitable for creating a measure that correctly calculates this row-level multiplication and then sums the results?
- ASUMX (Sales, Sales[Quantity] * Sales[UnitPrice])
- BSUMMARIZE (Sales, Sales[OrderID], 'Total Revenue', SUM (Sales[Quantity] * Sales[UnitPrice]))
- CSUM (Sales[Quantity] * Sales[UnitPrice])
- DCALCULATE (SUM (Sales[Quantity] * Sales[UnitPrice]))
Show answer & explanationAnswer & explanation
Correct answer: A. SUMX (Sales, Sales[Quantity] * Sales[UnitPrice])
The SUMX function is specifically designed for row-level iteration and calculation. It iterates over each row of the specified table ('Sales' in this case), performs the expression for each row ('Quantity' * 'UnitPrice'), and then sums up the results. This correctly calculates the 'Total Revenue' by first determining the revenue for each individual line item.
Why the other options are wrong
- B. SUMMARIZE creates a new table and is not used for defining a simple measure that aggregates a row-level calculation.
- C. SUM expects a column reference and cannot perform row-level multiplication directly on two columns and then sum the results without an iterator.
- D. CALCULATE modifies context but doesn't enable row-level multiplication and summation in this way; it would still require an iterator like SUMX.
SUMX for Row-Level Calculations
A DAX iterator function that evaluates an expression for each row of a table and then sums the resulting values.
- Syntax: SUMX(<table>, <expression>).
- Essential for calculations that need to happen at the row level before aggregation (e.g., Price * Quantity).
- Creates its own row context for the expression.
Memory trick: SUMX iterates, calculates, then sums.