Concept
1. Filter context in one sentence
Every pivot cell has a set of filters — from its row, its column, slicers and report filters. A measure is calculated under those filters. The cell "North × Electronics, FY2025-26" sums only rows that match all three: 6,81,10,625.
2. CALCULATE = measure + changed filters
CALCULATE( <measure>, <filter1>, <filter2>, ... )
It takes the cell's filters, adds or replaces the ones you give, and evaluates the measure.
Fixed filter:
North Revenue := CALCULATE([Total Revenue], dim_Stores[Region] = "North")
In any cell, this shows North's revenue — even in the "South" row, because the Region filter is replaced. For FY2025-26: 9,42,56,153.
Several conditions (AND):
North Electronics := CALCULATE([Total Revenue], dim_Stores[Region] = "North", dim_Products[Category] = "Electronics")
OR within a column:
Delhi+Mumbai := CALCULATE([Total Revenue], dim_Stores[City] IN {"Delhi", "Mumbai"})
3. % of total — removing a filter with ALL
All Regions Revenue := CALCULATE([Total Revenue], ALL(dim_Stores))
Region Share % := DIVIDE([Total Revenue], [All Regions Revenue])
ALL(dim_Stores) removes every filter coming from the Stores table, so the denominator is always the total of all stores (still respecting Category, FY and other filters). North's share for FY2025-26 = 32.0%.
Share within category (ignore only the Product filter, keep Category):
Share of Category % := DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALL(dim_Products[Product])))
Laptop's share of Electronics = 46.4%.
REMOVEFILTERS(...) is a newer, more readable name for the same job as ALL inside CALCULATE.
4. Keep the existing filter: KEEPFILTERS
By default a CALCULATE filter overrides the cell's filter on that column. To intersect instead:
North Only := CALCULATE([Total Revenue], KEEPFILTERS(dim_Stores[Region] = "North"))
Now the South row shows blank (South AND North = nothing) instead of North's number.
5. Filtering on a measure: FILTER
A condition on a column is easy. For conditions on a calculation, use FILTER over a table:
Big Order Revenue := CALCULATE([Total Revenue], FILTER(fact_Sales, fact_Sales[Line Revenue] >= 50000))
FILTER iterates rows, so use it on small tables or when a simple column filter can't express the rule.
6. Reading CALCULATE in plain words
CALCULATE([Total Revenue], ALL(dim_Stores), dim_Products[Category] = "Kitchen")
→ "Total revenue, for all stores, but only Kitchen — plus whatever date/other filters the cell already has."
Common mistakes
Expecting [North Revenue] to be blank in other regions (it replaces the filter — use KEEPFILTERS if that's what you want). Using ALL on the fact table when you meant one dimension. Writing CALCULATE(SUM(...)) everywhere instead of building on base measures.