3.1CALCULATE, filter context & variables (VAR)
The essentials in 30 seconds
- Every cell of a visual has its own filter context: the "Paris × January" cell of a matrix computes [Total Sales] filtered on Paris AND January, without your writing anything.
- CALCULATE modifies that context: it adds a filter, overrides one, or removes one with ALL — the key to "% of total".
- VAR/RETURN structure any formula longer than one line: readability, performance and easier debugging.
Running mini-case — Zineb reads her cells out loud in Algiers
Zineb, a management controller in an agri-food SME in Algiers, displays a City × Month matrix of her [Total Sales] and is puzzled by a cell that looks wrong. She applies the section's method: instead of rewriting the measure, she reads the context out loud — "this cell = sales filtered on Lyon AND January" — and understands that each cell has its own. She then adds Sales All cities = CALCULATE([Total Sales]; ALL(Sales[City])): the same value everywhere, the city filter removed. Then she builds each city's share with a variable: Share city = VAR Total = CALCULATE([Total Sales]; ALL(Sales[City])) RETURN DIVIDE([Total Sales]; Total). She tests with an active Salesperson slicer: the share recalculates within the slicer's scope — exactly what she wants to explain to her management.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 3.1.
1. The filter context
Here is the chapter that separates the user from the DAX practitioner. Every calculation is evaluated in a filter context: the "Paris × January" cell of a matrix computes [Total Sales] filtered on Paris AND January — without your writing anything. Every cell of a visual has its own: axes, legends, slicers and filters add to it.
2. CALCULATE, ALL and VAR
CALCULATE MODIFIES that context: it adds a filter, overrides one (the share of total) or removes one with ALL. ALL(Sales[City]) ignores the city filter and gives the grand total — it is the key to the most requested calculation: the "% of total".
3. Structuring and diagnosing
We wrap it all in VAR/RETURN variables: the overall total is calculated once, the formula reads in one go and debugging becomes simple. The diagnostic reflex fits in one sentence: a "strange" DAX result is almost always a misunderstood context — Zineb reads it out loud before touching the formula.
🔎 Concrete example — 🔎 Concrete example — The % of total
The most requested calculation, in short, readable formulas:
Revenue All Cities = CALCULATE([Total Revenue], ALL(Sales[City]))
City Share =
VAR Total = CALCULATE([Total Revenue], ALL(Sales[City]))
RETURN DIVIDE([Total Revenue], Total) // formatted as %Reading: ALL(Sales[City]) removes the city filter: the measure displays the same value on every row — the total for all cities.
Lesson: DIVIDE([Total Sales]; Total) gives each city's share, recalculated within the scope of the active slicers.
Section vocabulary
- Filter context
- The set of filters active on a visual cell (axes, legends, slicers, filters): each cell has its own.
Simple example: Zineb states: "the Lyon × January cell = sales filtered on Lyon and January".
What it is not: a single calculation valid for the whole visual.
Reflex: strange result → read the context out loud.
- CALCULATE
- Evaluates an expression in a modified context: it adds or overrides a filter.
Simple example: Zineb forces a filter on a city thanks to CALCULATE to isolate her result.
What it is not: a measure that undergoes the context without modifying it.
Reflex: modify a filter → CALCULATE.
- ALL
- Removes filters: ALL(Sales[City]) ignores the city filter — the key to "% of total".
Simple example: Zineb calculates the sales for all cities to compare each city with the total.
What it is not: a filter that stays active and skews the share.
Reflex: % of total → ALL on the column.
- VAR / RETURN
- DAX variables: you name an intermediate calculation once, then return it with RETURN.
Simple example: Zineb writes VAR Total = ... RETURN DIVIDE([Total Sales]; Total).
What it is not: the same calculation repeated three times in a formula.
Reflex: more than one line → VAR/RETURN.
- Context transition
- The advanced mechanism by which CALCULATE transforms a row context into a filter context.
Simple example: Zineb knows how to name it and plans to dig into it with the SQLBI reference.
What it is not: the simple filter context of a visual cell.
Reflex: to know by name, to explore later.
In practice — In practice — The "% of total" dissected
- Create a City × [Total Sales] matrix and READ each cell by stating its context: "this cell = sales filtered on Lyon", like Zineb.
- Write Sales All cities = CALCULATE([Total Sales]; ALL(Sales[City])) and add it to the matrix: the same value everywhere (the city filter is removed).
- Assemble with variables: Share city = VAR Total = CALCULATE([Total Sales]; ALL(Sales[City])) RETURN DIVIDE([Total Sales]; Total), then format in %.
- Test with an active Salesperson slicer: the share recalculates WITHIN the slicer's scope — reread the formula until you can explain why.
Step-by-step script — read a context like Zineb (8 min)
1) Display [Total Sales] in a matrix, City in rows and Month in columns. 2) Click a cell and state its context out loud: the crossing of the two active filters. 3) Create Sales All cities = CALCULATE([Total Sales], ALL(Sales[City])): verify that the value is the same on every row. 4) Create Share city with VAR/RETURN: VAR Total = CALCULATE([Total Sales], ALL(Sales[City])) RETURN DIVIDE([Total Sales], Total), formatted in %. 5) Place a Salesperson slicer: observe the share recalculating within the scope, and explain it out loud.
If a total looks wrong, go back to step 2: nine times out of ten, the context explains the result.
Copy-paste template — my context measures
Revenue All Cities = CALCULATE([Total Revenue], ALL(Sales[City]))
City Share =
VAR Total = CALCULATE([Total Revenue], ALL(Sales[City]))
RETURN DIVIDE([Total Revenue], Total) // %
Rule: read the context → modify it (CALCULATE/ALL) → structure it (VAR/RETURN).Share of total
Zineb displays each city's share of the overall sales: the combo CALCULATE + ALL + VAR, formatted in %.
Total for all cities
Zineb isolates the grand total with ALL(Sales[City]): the same value repeated on every row, a constant reference.
Diagnosing a cell
A doubtful result: Zineb reads the context out loud before touching the measure — the bug is often there.
A readable formula
A five-line measure becomes clear thanks to VAR: the calculation named once, RETURN at the end.
✓ Best practices
- Read a cell's context out loud to understand a result.
- Use VAR/RETURN systematically as soon as a formula is longer than one line.
- Start every diagnosis with the filter context.
✗ Common pitfalls
- Writing unreadable nested CALCULATEs with no variables.
- Confusing ALL (removes everything) and ALLSELECTED (respects slicers).
- Looking for a bug anywhere but in the context.
Key takeaways
- Every visual cell has its filter context: read it out loud to understand a result.
- CALCULATE adds/overrides filters; ALL removes them: the two gestures cover most needs.
- VAR/RETURN systematic as soon as a formula is longer than one line: readability AND performance.
- A "strange" DAX result = a misunderstood context: the diagnosis always starts there.
48h challenge — apply it tomorrow
Reproduce Zineb's matrix: [Total Sales] by City, then add CALCULATE([Total Sales]; ALL(Sales[City])) and the Share city measure built with VAR/RETURN. Write in one sentence why the share recalculates when a slicer is set.
- I can read a visual cell's filter context.
- I can write the % of total with CALCULATE + ALL + VAR.
- I diagnose a DAX result by starting from the context.
Frequently asked questions
ALL, ALLSELECTED, ALLEXCEPT: how do I find my way?
ALL removes any filter on the target (the grand total); ALLSELECTED respects external selections, such as slicers; ALLEXCEPT removes everything except the columns cited. Start with ALL, move to ALLSELECTED when the % must follow the slicers, and keep ALLEXCEPT for later — the fine cases become clearer with practice and the SQLBI reference.
Why is my measure right in the detail but wrong on the Total row?
Because the Total row has a wider context: no city filter is active there. A poorly built measure degrades there — the classic case is the average of ratios. The usual solution is to recalculate by iteration with SUMX rather than averaging an already aggregated result. Reflex: right in the detail, wrong in the total → suspect a misplaced ratio.
What is ALL for in CALCULATE?