How to Use ALLEXCEPT in Power BI

Ever tried creating a ‘percentage of total’ calculation in Power BI, only to have your slicers and filters mess up the result? You're not alone. Mastering control over your calculations as users interact with your report is a common hurdle. This is exactly where the DAX function ALLEXCEPT becomes an essential tool in your Power BI toolkit. It allows you to create stable, accurate calculations that respond precisely how you want them to.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

This tutorial will walk you through what the ALLEXCEPT function is, why it's so important for dynamic reporting, and how you can use it with step-by-step examples. We'll break down the syntax and show you how to build powerful measures that give you accurate insights.

What is the ALLEXCEPT Function in DAX?

At its core, ALLEXCEPT is a table manipulation function that modifies the "filter context" of your calculations. In simpler terms, it tells your Power BI measure to remove all filters from a table, except for the filters applied to specific columns you list.

Think of it as a precision tool. While other functions might remove all filters entirely, ALLEXCEPT lets you surgically protect certain filters while clearing out all the rest. This is incredibly useful for calculating percentages and ratios where you need a stable denominator that only changes based on specific criteria.

Understanding the ALLEXCEPT Syntax

The syntax for ALLEXCEPT is straightforward, which makes it easy to learn. It looks like this:

ALLEXCEPT(<,table>, <column1>, <column2>, ...)

Let’s break down its parts:

• <,table>: This is the first and only required argument. You specify the table from which you want to remove filters. This is often your main fact table, like a ‘Sales’ or ‘Transactions’ table.
• <column1>, <column2>, ...: These are the columns whose filters you want to keep. Any other filters applied to other columns in the table (through slicers, other visuals, or the report canvas) will be ignored by the calculation.

The key takeaway is this: the columns you list in the function are the exceptions – their filters will be preserved, and everything else gets cleared away.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why ALLEXCEPT Really Matters

To understand the power of ALLEXCEPT, let’s imagine a common business scenario. You're building a sales report with a table showing revenue by Product Subcategory. You also have slicers on the page for Year, Country, and Product Category.

Your goal is to add a new column that shows each Subcategory's contribution to its parent Category's total sales. For example, if a user filters for "Bikes," you want to see what percentage of all bike sales came from "Mountain Bikes," "Road Bikes," etc.

Without ALLEXCEPT, your typical percentage calculation might look like this:

(Sales for the Subcategory) / (Total Sales)

The problem is, if a user also selects the "2023" slicer, the calculation becomes:

(2023 Sales for the Subcategory) / (2023 Total Sales for the Subcategory)

The numerator and denominator are the same, so your result is always 100%! This is where ALLEXCEPT steps in. You can use it to create a denominator that respects the 'Product Category' filter but ignores the 'Year' filter. This gives you the right calculation every time.

ALLEXCEPT vs. ALL vs. REMOVEFILTERS

Power BI has several functions that modify filter context, and it's easy to get them confused. Let’s clarify how ALLEXCEPT differs from its cousins, ALL and REMOVEFILTERS.

• ALL(): This function is the sledgehammer. When used, it removes all filters from the specified table or columns. For example, CALCULATE(SUM(Sales[Revenue]), ALL(Sales)) will give you the grand total of all sales, regardless of any slicers or filters applied to the report.
• REMOVEFILTERS(): This is simply the modern, more readable name for ALL(). They do the same thing when used as a filter modifier inside CALCULATE. It's generally considered best practice to use REMOVEFILTERS because it makes your DAX easier to understand.
• ALLEXCEPT(): This is your precision tool. Instead of removing all filters, it removes only the ones you don't tell it to keep. It gives you far more granular control over your calculations than ALL or REMOVEFILTERS.

In short: use ALL or REMOVEFILTERS when you need a grand total denominator. Use ALLEXCEPT when you need a subtotal denominator, like "total sales for a specific category" or "total revenue for a particular country."

How to Use ALLEXCEPT in Power BI: A Step-by-Step Example

Let's build that 'percent of category total' measure we discussed earlier. Imagine you have a data model with a ‘Sales’ table containing [Revenue], [Product Category], [Product Subcategory], and [Order Year].

Our goal is to create a visualization showing each Product Subcategory and its percentage contribution to its parent Category's sales, ignoring any Year filter.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Create a Base Measure for Total Sales

First, it's always good practice to have a base measure for your core value. This makes your more complex DAX formulas easier to write and debug.

In the DAX formula bar, create a new measure:

Total Sales = SUM(Sales[Revenue])

Step 2: Create the Denominator Measure Using ALLEXCEPT

Next, we need to create the denominator for our percentage calculation. This measure will calculate the total sales for whatever Product Category is being viewed, removing all other context filters from the Sales table (like Year or Subcategory).

Create a new measure:

Category Total Sales = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(
        Sales,
        Sales[Product Category]
    )
)

Let's analyze what's happening here:

• The CALCULATE function tells Power BI we want to modify the context of our [Total Sales] measure.
• ALLEXCEPT(Sales, Sales[Product Category]) is our filter modifier. It returns the entire ‘Sales’ table, but with all filters removed except any active filter on the Sales[Product Category] column.

When you put this measure in a table with ‘Product Category’ and ‘Product Subcategory,’ it correctly calculates the total for the entire active category.

Step 3: Create the Final Percentage Measure

Now we just need to divide our base sales measure by our new denominator measure.

Create a final measure:

% of Category Total = 
DIVIDE(
    [Total Sales],
    [Category Total Sales]
)

It's best practice to use the DIVIDE() function instead of the slash operator (/) because it automatically handles division-by-zero errors without you needing to write extra IF statements.

Step 4: Visualize Your Results

Now, create a table or matrix visual. Drag [Product Category], [Product Subcategory], [Total Sales], and the new [% of Category Total] measure into the visual. Be sure to format the percentage measure as a percentage from the Measure tools ribbon.

You'll see that for each Product Category, the percentages add up to 100%. Now test your slicers! If you filter by 'Year', you'll notice that the [Total Sales] column updates, but the [% of Category Total] calculation remains correct, showing the subcategory's share within its filtered parent category for that year.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Advanced Example: Keeping Multiple Filters with ALLEXCEPT

The real power of ALLEXCEPT comes from its ability to preserve more than one filter. Let's say you want to calculate each subcategory's percentage of sales within its parent Category and Country.

All you need to do is add another column to the ALLEXCEPT function. Suppose your 'Sales' table also contains a Geography[Country] column. The denominator would be:

Country & Category Total = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(
        Sales, 
        Sales[Product Category], 
        Geography[Country]
    )
)

This measure now calculates the total sales for the product category a user has selected and the country a user has selected, while ignoring all other filters like year, region, or city. This technique forms the foundation for building highly interactive and insightful dashboards.

Common Mistakes to Avoid

When you're first learning to use ALLEXCEPT in Power BI, it's easy to make a few common mistakes. Here’s what to watch out for:

• Thinking it adds filters: The name is ALLEXCEPT, implying it keeps some things. But remember, its primary job is to remove filters. The columns you list are just the exceptions that are left behind.
• Forgetting to use CALCULATE: ALLEXCEPT is a table function. On its own, it just returns a modified table. To use it to change how a measure is aggregated, it almost always needs to be placed inside a CALCULATE function.
• Confusing the arguments: The first argument is the entire table. The following arguments are the columns whose filters you want to keep active. Make sure you don't flip them!

Final Thoughts

Mastering ALLEXCEPT in Power BI lifts you out of basic reporting and into crafting truly dynamic and insightful analytical models. This function is essential for building robust reports that give users accurate context, especially when calculating percentages, Period-over-Period analyses, or share-of-total metrics. With it, you gain precise control over your filter context and can guide the story your data tells.

While mastering DAX can be powerful, we know that getting answers from your data shouldn't always require learning a programming language. That's why we created Graphed—it's an AI data analyst that allows you to connect your sources — like Google Analytics, Shopify, and Salesforce — and simply ask for what you need in plain English. Instead of writing complex measures, you could ask, "Show me what percentage of each product category's sales last month came from each subcategory," and instantly get a live, interactive dashboard that answers your question in seconds.