ALLEXCEPT
Removes every filter on a table except the filters on the columns you list.
ALLEXCEPT(<table>, <column> [, <column> ...])Returns: A table. Used inside CALCULATE, it keeps only the named column filters.
What it does
ALLEXCEPT clears all the filters on a table except the ones on the columns you name. Use it to compare an item with its own group: a store with its country, or a product with its category. The grouping column keeps its filter, and every other column in the table is cleared.
Syntax and arguments
- table: the table to clear. It must be a table, not a single column.
- column (one or more): the columns whose filters stay in place.
Example
A report lists stores. Each store's share should be measured against all stores in the same country.
Share of Country Sales =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALLEXCEPT ( Stores, Stores[Country] ) )
)
The Country filter stays because it is named. The store name and store type filters are cleared, so the denominator is the country total. Within one country, the store shares add up to 100 percent.
Example: a second use
Each product's share of its own category.
Share of Category Sales =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALLEXCEPT ( Product, Product[ProductCategory] ) )
)
The category filter stays, and the product name filter is cleared. This shows mix within a category, which is what a product manager usually wants to see.
Things to watch
- The first argument must be a table.
ALLEXCEPT(Product[ProductCategory], ...)is not valid. - Leave out a grouping column and the denominator widens, so the shares no longer add to 100 percent within the group.
- Filters that come through relationships still apply. A date slicer still filters Sales.
- ALLEXCEPT ignores slicers on the cleared columns. If the reader expects the visible selection to set the base, use
ALLSELECTEDwith the group column. - Compare with
ALL, which clears everything in the table, andREMOVEFILTERS, which names what to clear.
In business terms
Shares within a segment: a branch's share of its region, an account's share of its class, a cost center's share of its department. Budget and variance packs use it to show mix inside each group while keeping the group total as the base.