Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data analyst is building a Power BI model for a global sales company. The model includes a 'Sales' table and a 'Currency Exchange Rates' table. The 'Sales' table records sales amounts in various local currencies, and the 'Currency Exchange Rates' table provides daily exchange rates to a common reporting currency (USD). The analyst needs to calculate the total sales in USD for each transaction. Which DAX function is most appropriate for a row-level currency conversion within a measure?
- ASUMX
- BCALCULATE
- CLOOKUPVALUE
- DRELATED
Show answer & explanationAnswer & explanation
Correct answer: A. SUMX
SUMX is an iterator function that evaluates an expression for each row of a table and then sums the results. This is ideal for row-level calculations like multiplying a sales amount by an exchange rate for each individual sales transaction before summing them up.
Why the other options are wrong
- B. CALCULATE modifies filter context and is essential for many DAX calculations, but it doesn't inherently iterate row by row for a sum of products.
- C. LOOKUPVALUE is used to return a single value from a table where all criteria are met, but it's not designed for iterating and summing an expression across multiple rows.
- D. RELATED is used to retrieve a single value from the 'one' side of a one-to-many relationship, effective within a calculated column or another row context, but not for iterating and summing.
SUMX for Row-Level Aggregation
The SUMX function iterates over each row of a specified table, evaluates an expression for that row, and then sums up the results of these evaluations.
- An iterator function (X-function).
- Performs calculations at a row context.
- Ideal for situations where a calculation needs to happen per row before aggregation, like weighted averages or currency conversion.
Memory trick: X-functions for eXact row calculations.