Power BI DAX ALL and REMOVEFILTERS
Percent of Total
A common question in reports: what share of the sales does each region have?
For Johto, the answer is the Johto sales divided by the sales of all regions.
The first part is easy: Total Sales in the Johto row.
The second part is the problem.
The filter context of the Johto row is Region = "Johto", so Total Sales only sees Johto.
You need a way to remove that filter.
That is what the ALL function does.
This page uses the Poke Mart model described in DAX Introduction, with the Total Sales measure from DAX Measures.
The ALL Function
ALL returns a whole table, or all the values of a column, and ignores any filters on them.
Used as a filter in CALCULATE, it removes the filters from that table or column.
Syntax
ALL(table)
ALL(column [, column, ...])
| Argument | Meaning |
|---|---|
| table | The table to remove all filters from, like Stores. |
| column | A column to remove filters from, like Stores[Region]. You can list several columns, if they are from the same table. |
Try it with a helper measure first:
Example
All Regions Sales = CALCULATE([Total Sales], ALL(Stores))
In a matrix with Region in Rows:
| Region | Total Sales | All Regions Sales |
|---|---|---|
| Hoenn | 2,972,000 | 9,416,800 |
| Johto | 3,363,650 | 9,416,800 |
| Kanto | 3,081,150 | 9,416,800 |
| Total | 9,416,800 | 9,416,800 |
ALL(Stores) removes the Region filter from each row, so every row shows the sales of all regions.
Kanto Sales on the CALCULATE page replaced the filter. ALL removes it.
% of All Regions
Now divide the two.
Create this measure, and format it as Percentage with 2 decimal places:
Example
% of All Regions = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Stores)))
| Region | Total Sales | % of All Regions |
|---|---|---|
| Hoenn | 2,972,000 | 31.56% |
| Johto | 3,363,650 | 35.72% |
| Kanto | 3,081,150 | 32.72% |
| Total | 9,416,800 | 100.00% |
The three regions add up to 100%.
Johto is the biggest region, with 35.72% of all sales.
The formula uses DIVIDE, which returns a blank instead of an error if the second number is 0.
Other Filters Still Apply
ALL(Stores) only removes filters from the Stores table.
Filters on other tables stay.
Add a slicer with Year from the Date table.
A slicer on a number column like Year starts as a Between slider, so go to Format visual > Visual > Slicer settings > Options and set Style to Vertical list. Then select 2025:
| Region (2025) | Total Sales | % of All Regions |
|---|---|---|
| Hoenn | 1,647,300 | 32.79% |
| Johto | 1,751,200 | 34.86% |
| Kanto | 1,625,100 | 32.35% |
| Total | 5,023,600 | 100.00% |
The Year filter is kept, so each region is now divided by the total of 2025 (5,023,600).
The shares still add up to 100%, within the year.
Clear the slicer before you go on.
ALL vs ALLEXCEPT
Now look at the stores.
Put Region and Store in the Rows of the matrix, with Region above Store, and add % of All Regions.
If you tick Store in the Data pane, Power BI puts it in Columns: drag it into Rows, below Region.
Then select Expand all down one level in the hierarchy (the forked arrow at the top of the matrix) to show the stores under each region.
Each store is compared with all six stores.
What if you want each store's share of its own region instead?
Then you must remove the Store filter, but keep the Region filter. Use ALLEXCEPT:
Syntax
ALLEXCEPT(table, column [, column, ...])
ALLEXCEPT removes all filters from the table, except the filters on the columns you list.
Example
% of Region = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLEXCEPT(Stores, Stores[Region])))
| Region / Store | Total Sales | % of All Regions | % of Region |
|---|---|---|---|
| Hoenn | 2,972,000 | 31.56% | 100.00% |
| Lilycove City | 1,373,450 | 14.59% | 46.21% |
| Slateport City | 1,598,550 | 16.98% | 53.79% |
| Johto | 3,363,650 | 35.72% | 100.00% |
| Goldenrod City | 2,280,550 | 24.22% | 67.80% |
| Violet City | 1,083,100 | 11.50% | 32.20% |
| Kanto | 3,081,150 | 32.72% | 100.00% |
| Cerulean City | 1,666,550 | 17.70% | 54.09% |
| Viridian City | 1,414,600 | 15.02% | 45.91% |
| Total | 9,416,800 | 100.00% | 100.00% |
Look at Goldenrod City. % of All Regions divides its sales by the sales of all stores (9,416,800): 24.22%. % of Region divides them by the Johto sales (3,363,650): 67.80%.
Goldenrod City alone makes more than two thirds of the Johto sales.
| Filter in CALCULATE | In the Goldenrod City row | Divides by |
|---|---|---|
ALL(Stores) | Removes the Store and the Region filter | 9,416,800 |
ALLEXCEPT(Stores, Stores[Region]) | Removes the Store filter, keeps Region = Johto | 3,363,650 |
Note: ALL(Stores[Region]) would remove only the filter on the Region column.
In the store rows, the Store filter would stay, and every store would show 100%.
That is why % of All Regions uses ALL(Stores), the whole table.
REMOVEFILTERS
REMOVEFILTERS does the same job as ALL inside CALCULATE.
Its name says exactly what it does.
Syntax
REMOVEFILTERS([table])
REMOVEFILTERS(column [, column, ...])
This measure gives exactly the same results as % of All Regions:
Example
Region Share = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Stores)))
In the matrix by Region, Region Share shows 31.56%, 35.72% and 32.72%, and 100.00% in the Total row.
The difference between the two functions:
| ALL | REMOVEFILTERS | |
|---|---|---|
| Removes filters inside CALCULATE | Yes | Yes |
Can return a table, for example COUNTROWS(ALL(Stores)), which returns 6 | Yes | No |
Microsoft recommends REMOVEFILTERS when you only want to remove filters.
You will still see ALL in many formulas, including % of All Regions in this tutorial, and inside CALCULATE it works the same way.
With no arguments, REMOVEFILTERS() removes all filters from all tables.
What You Learned
- ALL, used in CALCULATE, removes filters from a table or from columns.
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Stores)))gives each region's share, and the shares add up to 100%.- Filters on other tables, like a Year slicer, still apply.
- ALLEXCEPT removes all filters from a table except the ones you list.
- REMOVEFILTERS is the clearer name for removing filters. Unlike ALL, it cannot return a table.