Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX ALL and REMOVEFILTERS


Share

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, ...])
ArgumentMeaning
tableThe table to remove all filters from, like Stores.
columnA 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:

RegionTotal SalesAll Regions Sales
Hoenn2,972,0009,416,800
Johto3,363,6509,416,800
Kanto3,081,1509,416,800
Total9,416,8009,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)))
RegionTotal Sales% of All Regions
Hoenn2,972,00031.56%
Johto3,363,65035.72%
Kanto3,081,15032.72%
Total9,416,800100.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
Hoenn1,647,30032.79%
Johto1,751,20034.86%
Kanto1,625,10032.35%
Total5,023,600100.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 / StoreTotal Sales% of All Regions% of Region
Hoenn2,972,00031.56%100.00%
   Lilycove City1,373,45014.59%46.21%
   Slateport City1,598,55016.98%53.79%
Johto3,363,65035.72%100.00%
   Goldenrod City2,280,55024.22%67.80%
   Violet City1,083,10011.50%32.20%
Kanto3,081,15032.72%100.00%
   Cerulean City1,666,55017.70%54.09%
   Viridian City1,414,60015.02%45.91%
Total9,416,800100.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 CALCULATEIn the Goldenrod City rowDivides by
ALL(Stores)Removes the Store and the Region filter9,416,800
ALLEXCEPT(Stores, Stores[Region])Removes the Store filter, keeps Region = Johto3,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:

ALLREMOVEFILTERS
Removes filters inside CALCULATEYesYes
Can return a table, for example COUNTROWS(ALL(Stores)), which returns 6YesNo

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.


Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.