Microsoft Certified: Power BI Data Analyst AssociateModel the dataEasy
A data analyst is designing a Power BI data model for a global retail company. The model needs to support complex financial reporting, including the calculation of 'Gross Profit' which is defined as 'Sales Amount' minus 'Cost of Goods Sold'. Both 'Sales Amount' and 'Cost of Goods Sold' are measures already defined in the model. The analyst wants to ensure that the 'Gross Profit' calculation is dynamic and responds correctly to all filters applied to the report. Which of the following DAX expressions should the analyst use to calculate 'Gross Profit'?
- AGross Profit = [Sales Amount] - [Cost of Goods Sold]
- BGross Profit = SUM(Sales[SalesAmount]) - SUM(Sales[CostOfGoodsSold])
- CGross Profit = SELECTEDVALUE(Sales[SalesAmount]) - SELECTEDVALUE(Sales[CostOfGoodsSold])
- DGross Profit = CALCULATE(SUM(Sales[SalesAmount]) - SUM(Sales[CostOfGoodsSold]))
Show answer & explanationAnswer & explanation
Correct answer: A. Gross Profit = [Sales Amount] - [Cost of Goods Sold]
When measures are already defined, they can be directly referenced within other measures using their names. This approach ensures that the underlying logic of 'Sales Amount' and 'Cost of Goods Sold' measures, including any implicit or explicit filter contexts, is correctly applied to the 'Gross Profit' calculation.
Why the other options are wrong
- B. This option re-aggregates the columns, which is unnecessary and potentially incorrect if 'Sales Amount' and 'Cost of Goods Sold' measures involve more complex logic than simple sums.
- C. SELECTEDVALUE is used to return a single value when the context is filtered down to one distinct value. It's not appropriate for combining aggregated measures.
- D. CALCULATE is not needed here as the measures already handle their own context. Wrapping them in CALCULATE without a filter argument is redundant and doesn't change the outcome for this specific scenario.
Measure Referencing
Measures can be directly referenced by their names within other DAX measures. This allows for modularity and reusability, ensuring that the referenced measure's logic and context transitions are inherited.
- Simplifies complex calculations.
- Ensures consistent logic across measures.
- Automatically respects filter context.
Memory trick: Combine existing pieces, don't rebuild them from scratch.