3.2.1. Single Aggregation Measures and Quick Measures
💡 First Principle: The simplest measures summarize one column with one aggregation, and they're the building blocks for everything else. Write them explicitly, name them clearly, format them once, and build complex measures by referencing them rather than repeating their logic.
Core aggregation functions:
| Function | Returns | Note |
|---|---|---|
SUM(column) | Total of a numeric column | Ignores blanks |
AVERAGE(column) | Arithmetic mean | Ignores blanks, includes zeros |
MIN / MAX | Smallest / largest value | Also works on dates and text |
COUNT(column) | Count of non-blank values | Numbers, dates, text |
COUNTA(column) | Count of non-blank values of any type | |
COUNTROWS(table) | Number of rows in a table | Preferred for counting records |
DISTINCTCOUNT(column) | Number of distinct values | Includes blank as a value if present |
COUNTBLANK(column) | Number of blank values |
Examples: Total Sales = SUM(Sales[SalesAmount]), Order Count = COUNTROWS(Sales), Customers = DISTINCTCOUNT(Sales[CustomerKey]), Margin % = DIVIDE([Total Margin], [Total Sales]). Note that the last measure references other measures, which keeps logic in one place.
Iterators (SUMX, AVERAGEX, MINX, MAXX, COUNTX) compute an expression per row and then aggregate, which is how you calculate revenue from quantity and price without storing a column: Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]).
Quick measures (right-click a table or field > New quick measure) generate DAX from a dialog for common patterns: aggregate per category, filtered value, difference from baseline, year-over-year change, running total, rolling average, star rating, and more. Recent versions also let you describe the measure in natural language with Copilot, depending on capacity and settings. Quick measures produce ordinary DAX you can read and edit, which makes them a good learning tool and a "minimize development effort" answer.
Formatting and organization: set the format string on the measure (currency, percentage), add a description, and place measures in display folders or a measures table. Implicit measures, created by dragging raw columns, can't be referenced by other measures or used in calculation groups.
⚠️ Exam Trap: COUNT(Sales[OrderID]) and COUNTROWS(Sales) differ when OrderID has blanks, and DISTINCTCOUNT(Sales[CustomerKey]) counts customers, not transactions. Read the stem's noun carefully: "number of orders," "number of order lines," and "number of customers who ordered" are three different measures.
Reflection Question: Why is Margin % = DIVIDE([Total Margin], [Total Sales]) correct at every level of a matrix, while AVERAGE(Sales[MarginPct]) over a stored per-row percentage column isn't?
