A PivotTable filters your data. A slicer narrows it further. Most DAX measures respect those filters automatically — that is precisely the point of filter context. However, some calculations need to deliberately ignore one or more filters. ALL removes every filter from a table or column. ALLEXCEPT removes all filters except the ones you name. Together, these two functions power percentage-of-total and ranking calculations that need a fixed denominator regardless of the user’s current filter state.
This guide covers how ALL and ALLEXCEPT work. It also explains the dual-context behaviour and six practical examples: market share, percentage of grand total, category-level percentages, rank inside a filtered view, cumulative Pareto share, and a variance-from-company-average measure.
How Do ALL and ALLEXCEPT Work?
ALL(TableOrColumn) returns all rows of a table or all distinct values of a column. Importantly, it ignores any active filters completely. When used as a CALCULATE argument, ALL removes that filter before evaluating the expression. ALLEXCEPT(Table, Column1...) works similarly but preserves the specified columns’ filters. All other filters on the table are then removed.
| Function | As CALCULATE argument | As standalone expression |
|---|---|---|
| ALL(Table) | Removes all filters on the table | Returns the full unfiltered table |
| ALL(Table[Col]) | Removes filter on that column only | Returns all distinct column values |
| ALLEXCEPT(T, C1) | Removes all filters except C1 | Returns table with only C1 filter kept |
| ALLSELECTED(T) | Removes row filters, keeps slicer context | Returns rows visible in current visual |
The Core Percentage-of-Total Pattern
The most fundamental ALL pattern divides the current filtered value by the grand total. CALCULATE with ALL removes the row context filter. Consequently, the denominator is always the full dataset total. The percentages therefore add up to 100% across all PivotTable rows.
Examples 1–4: ALL and ALLEXCEPT in Practice
Each region row should show its share of company-wide revenue. The filter context applies the Region filter automatically. ALL(Sales) in the denominator removes that filter, so the denominator is always the full total — identical for every row.
When a PivotTable shows Region and Product together, the percentage denominator should be the total for that product across all regions — not the grand total. ALLEXCEPT removes the Region filter while keeping the Product filter. Specifically, the denominator changes per product row.
RANKX alone ranks against all table rows. ALLSELECTED makes RANKX rank only against the currently visible rows after slicers are applied. Additionally, the rank updates in real time when the slicer changes. This is therefore the correct approach for interactive report ranking.
A Pareto chart needs a running cumulative percentage. This measure ranks all products by sales, sums all products at or above the current rank, then divides by the ALL grand total. Specifically, it shows which products together account for 80% of revenue.
Examples 5–6: Column-Level ALL and Variance Measures
ALL applied to one column removes only that column’s filter. ALL(Sales[Date]) removes date slicers while keeping Region and Product filters. This creates a full-year reference metric that appears alongside the current-period value in the same row for easy comparison.
Each region’s average order value compared to the company-wide average is a useful performance metric. ALL in the denominator removes the row filter. Consequently, the company average is stable and identical across every PivotTable row.
Common Issues and How to Fix Them
The measure still changes with the slicer despite using ALL
Check which table or column you passed to ALL. ALL(Sales[Region]) removes only the Region filter — other active filters still apply. To remove all filters on the table, use ALL(Sales) without a column argument. Alternatively, list every column you want to remove filters from individually.
The percentages do not add up to 100%
The denominator is likely recalculating per row instead of holding a constant total. Verify that you are using ALL(Sales) — not ALL(Sales[Region]) — in the denominator. Also check that you are using DIVIDE rather than a raw division. DIVIDE returns blank for zero denominators, which can cause the total row to display incorrectly.
Frequently Asked Questions
-
What does ALL do in DAX?+ALL removes filters from a table or column. Inside CALCULATE, it evaluates the expression with those filters removed. CALCULATE([Total Sales], ALL(Sales)) returns the grand total regardless of any active filter. Used standalone inside FILTER or SUMX, ALL returns the full unfiltered table for iteration.
-
What is the difference between ALL and ALLEXCEPT?+ALL removes all filters from the specified table or column. ALLEXCEPT removes all filters except those on the columns you list. ALLEXCEPT(Sales, Sales[Product]) removes every filter on Sales except Product. Use ALLEXCEPT when the denominator should vary per category instead of being the absolute grand total.
-
What is ALLSELECTED and when should I use it?+ALLSELECTED removes row-level filters inside a visual while preserving outer slicer and cross-filter context. Use it when percentage or rank should reflect the user’s current filtered view. If a user filters to three regions using a slicer, ALLSELECTED makes those three regions sum to 100% instead of calculating against all eight in the model.
-
Can ALL be used outside of CALCULATE?+Yes. ALL(Table) or ALL(Table[Column]) returns an unfiltered table or column list usable inside FILTER or SUMX. FILTER(ALL(Sales[Product]), ...) iterates all products regardless of active filters. This differs from the CALCULATE-argument usage — as a CALCULATE argument, ALL modifies filter context; as a table expression, it provides unfiltered data for iteration.