Launch Deal · 50% off 1st month ·

2.5 — Single Aggregation Measures and the CALCULATE Function

PL-300 Domain 2 · Skill 2.5

What You Need to Know

Single aggregation measures

Function Returns
SUM(col), AVERAGE(col), MIN(col), MAX(col) The aggregate of a numeric column
COUNT(col) Non-blank values in a column
COUNTA(col) Non-blank values of any type
COUNTBLANK(col) Blank values
COUNTROWS(table) Rows in a table (the usual way to count orders or lines)
DISTINCTCOUNT(col) Distinct values, counting BLANK as a value
SUMX(table, expr), AVERAGEX… An iterator: evaluates expr row by row, then aggregates

Use an iterator when the value must be computed per row first: Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price]). This avoids storing a calculated column.

Filter context

Every cell in a visual has a filter context: the slicers, page and report filters, the row and column headers of the visual, and RLS. A measure is evaluated inside that context. Filters reach a table through relationships.

CALCULATE

CALCULATE(<expression>, <filter1>, <filter2>, …) evaluates an expression in a modified filter context. It's the most important DAX function.

Red Sales = CALCULATE([Total Sales], Product[Color] = "Red")
  • A simple filter like Product[Color] = "Red" replaces any existing filter on that column. Slicing by Color = Blue still shows red sales.
  • Wrap it in KEEPFILTERS to intersect with the existing filter instead: CALCULATE([Total Sales], KEEPFILTERS(Product[Color] = "Red")) returns blank for Blue.
  • Several filter arguments are combined with AND. Use || inside one argument, or IN { }, for OR on one column.
  • FILTER(table, condition) is needed when the condition uses a measure or compares columns: CALCULATE([Total Sales], FILTER(Customer, [Customer Sales] > 10000)).

Removing filters

Function Removes
ALL(table) / REMOVEFILTERS(table) Every filter on the table
ALL(col) / REMOVEFILTERS(col) Filters on that column only
ALLEXCEPT(table, col1, …) Every filter on the table except those on the listed columns
ALLSELECTED(…) Filters from inside the visual, but keeps slicer and filter-pane selections ("% of visible total")

Percent of total:

% of All Products = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product)))

Context transition

When CALCULATE is used where there's a row context (a calculated column, or inside an iterator), it turns the current row into a filter. That's why a measure referenced in a calculated column returns the value for that row, while a bare SUM in a calculated column returns the grand total.

Key Concept: CALCULATE is how you change what a measure sees: add filters, override them, keep them, or remove them with ALL, REMOVEFILTERS and ALLEXCEPT.

Exam Traps

  • Expecting CALCULATE([Sales], Product[Color] = "Red") to respect a slicer on Color (it overrides it unless you use KEEPFILTERS)
  • Using ALL when the percentage must respect the user's slicer selection (use ALLSELECTED)
  • Writing CALCULATE([Sales], [Profit] > 0) (a measure in a filter needs FILTER)
  • Counting orders with COUNT(Sales[OrderID]) when the table has one row per order line (use DISTINCTCOUNT on the order number, or COUNTROWS on an orders table)
  • Forgetting that DISTINCTCOUNT counts BLANK as one value

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 22 PL-300 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.

Already Pro? Log in.

Sources