PL-300 Model the data Practice Question
A Power BI developer has a fact table that contains sales data at the transaction level. The table includes columns: TransactionID, ProductID, CustomerID, DateKey, Quantity, UnitPrice, Discount, and SalesAmount. The developer wants to create a measure for total sales after discount. Which approach is best for performance and accuracy?
⚠ Common exam trap
Candidates often assume a DAX measure using SUMX or a simple subtraction of aggregated columns is equivalent in performance, but the exam tests the understanding that pre-calculating row-level logic in Power Query (M) is the most performant approach for large fact tables, while also ensuring mathematical accuracy.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Add a calculated column in Power Query: NetAmount = Quantity * UnitPrice - Discount, then create a measure: SUM(Sales[NetAmount])
It performs the net amount calculation at the row level in Power Query (M), which is computed during data refresh and stored in the table. This avoids runtime row-by-row iteration in DAX, making the measure SUM(Sales[NetAmount]) a simple, highly efficient aggregation. It ensures both performance and accuracy, as the discount is applied per transaction before aggregation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create a measure: SUM(Sales[SalesAmount]) - SUM(Sales[Discount])
Why it's wrong here
This measure independently aggregates the entire SalesAmount column and the entire Discount column before subtracting, so it loses the row-level pairing between a specific sale and its corresponding discount. It also fails to incorporate Quantity and UnitPrice, which are essential for computing gross sales. Consequently, the result is incorrect when discounts vary by transaction, because the total discount is applied as a single global adjustment rather than a per-row reduction.
- ✓
Add a calculated column in Power Query: NetAmount = Quantity * UnitPrice - Discount, then create a measure: SUM(Sales[NetAmount])
Why this is correct
Creating a calculated NetAmount column in Power Query (M) evaluates Quantity * UnitPrice - Discount once at refresh time, storing the result as a static column in the data model. The subsequent measure SUM(Sales[NetAmount]) simply aggregates those pre-computed values, avoiding row-by-row evaluation at report time and improving query performance for large fact tables. Because the calculation is pushed to the query engine instead of the DAX engine, it also keeps the code simpler and avoids iterator overhead during visual rendering.
- ✗
Create a measure: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice] - Sales[Discount])
Why it's wrong here
This SUMX measure correctly performs row-level multiplication and subtraction, but it does so by iterating through every row of the Sales table at query time, whenever the measure is evaluated inside a visual or filter context. For large fact tables, that row-by-row traversal can cause significant performance overhead, especially when the measure is used in multiple visuals or with varying filters. Unlike a calculated column, which stores the precomputed value, SUMX forces DAX to recompute the expression for every row each time it is invoked, making this approach less scalable and typically slower.
- ✗
Create a measure: SUM(Sales[Quantity] * Sales[UnitPrice]) - SUM(Sales[Discount])
Why it's wrong here
This measure aggregates the gross sales expression and the discount expression separately before subtracting, rather than pairing each row's discount with its corresponding Quantity * UnitPrice. As a result, the total discount is not aligned to the transactions it belongs to, which is especially problematic when some rows have no discount or discounts are applied at different rates. The correct DAX pattern would compute (Quantity * UnitPrice - Discount) at the row level and then sum, either through a measure like SUMX or a pre-calculated column, not by subtracting global aggregates.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 524 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.