What is the ALL Function in Power BI?

The ALL function in Power BI is one of the most important tools in your data analysis toolkit, letting you control exactly how your calculations behave. It’s what separates static, filtered views from dynamic reports that provide deep, comparative insights. This guide will walk you through what ALL does, why you need it, and how to apply it with clear, practical examples.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Filter Context and Why Does it Matter?

Before we can understand ALL, we have to talk about its counterpart: filter context. In Power BI, every calculation you see happens within a "context." This context is simply the set of active filters applied to your data at any given moment. When you click on a slicer, add a field to a visual, or cross-filter between charts, you are changing the filter context.

For example, if you have a bar chart showing sales by country, each bar calculates the total sales for its specific country. The filter context for the "USA" bar is "Country = USA." This is incredibly useful and is how Power BI works by default.

But what if you want to calculate what percentage of the total sales came from the USA? To do that, you need a way to find the sales for just the USA (which respects the filter context) and divide it by the sales for all countries (which must ignore the filter context). This is precisely the problem the ALL function solves.

The Core Job of the ALL Function

The ALL function gives you the power to remove or ignore the filter context. When used within a CALCULATE function, ALL creates a new, modified filter context for that specific calculation. It tells Power BI, "For this one calculation, pretend that this specific filter doesn't exist."

Essentially, it returns a table containing all of the rows from a table or all of the distinct values from a column, ignoring any filters that might have been applied.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Syntax of ALL

The syntax for the ALL function is straightforward:

ALL( {<table> | <column>[, <column>[, …]]} )

You can give it one of two things:

• A table name, like ALL(Sales): This removes all filters from every column in the entire 'Sales' table.
• One or more column names, like ALL(Products[Color]): This removes filters only from the specified column(s). All other filters on the table remain active.

On its own, ALL just returns a table of values. Its true power is unlocked when you use it as a filter modifier inside the CALCULATE function.

Using 'ALL' to Calculate Ratios and Percentages

The most common and understandable use for ALL is calculating percentages of a total. Let's walk through a step-by-step example.

Example: Calculating Percentage of Total Sales

Imagine you have a sales report and you've created a simple table visual showing sales broken down by product category. By default, it looks something like this:

Table Visual:

• Product Category
• Total Sales

To get this, you likely have a basic measure for sales:

Total Sales = SUM(Sales[SalesAmount])

Now, we want to add a third column showing what percentage of the grand total each category represents. To do this, we need to create two more measures.

Step 1: Create a Measure for the Grand Total

We need a measure that calculates the sum of all sales, completely ignoring the product category in each row of our visual. This is a perfect job for ALL().

Grand Total Sales = CALCULATE( [Total Sales], ALL(Sales) )

Let's break this down:

• CALCULATE([Total Sales], ...): We're telling Power BI to perform our Total Sales calculation, but with a modified filter context.
• ALL(Sales): This is the modifier. It instructs CALCULATE to remove all existing filters from the Sales table. So, even when sitting on the "Electronics" row of our visual, this measure will add up the sales for electronics, clothing, books, and everything else.

Step 2: Create the Percentage Measure

Now that we have both the sales for each category and the sales for all categories, we can divide them to find the percentage. It's best practice to use the DIVIDE function in DAX, as it safely handles cases where the denominator might be zero.

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

Now, add this new measure to your table visual and format it as a percentage. The result will look like this:

This works perfectly because for the "Electronics" row, [Total Sales] is calculated in the context of "Electronics" ($500k), while [Grand Total Sales] ignores that context and calculates the value for all categories ($1M). The result is $500k / $1M = 50%.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

A More Refined Approach: Using ALL on a Specific Column

Using ALL(Sales) was effective, but it was also a blunt instrument. It removed all filters from the Sales table. What if you added a slicer to your report to filter by year - say, "2023"? With our Grand Total Sales measure, the percentage would be calculated against the total sales from all time, because ALL(Sales) ignores the year slicer too.

Often, you want your calculation to respect some filters (like a year slicer) while ignoring others (like the category in the visual). In this case, you should specify the column you want ALL to ignore.

Here’s the improved measure:

Context-Aware Grand Total = CALCULATE( [Total Sales], ALL( Sales[Product Category] ) )

The logic is nearly the same, but the difference is critical. This version tells CALCULATE to remove only the filter on the Sales[Product Category] column. Any other filters - like a slicer on Sales[Year] or a filter on Sales[Region] - will still apply. This makes your reports much more dynamic and your measures far more flexible.

Going Beyond the Basics: ALLSELECTED and ALLEXCEPT

Once you're comfortable with ALL, you'll discover other related DAX functions that offer even more control over filter context.

When to Use ALLSELECTED

ALLSELECTED is useful when you want to calculate a percentage not of the grand total, but of a visible sub-total based on your slicer selections. It respects slicers and external filters but ignores the filters from within the current visual.

For example, if you have a slicer for product categories and you select "Electronics" and "Clothing," a measure using ALLSELECTED will calculate a percentage out of the combined total of just Electronics and Clothing sales, not the grand total of all categories.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

When to Use ALLEXCEPT

ALLEXCEPT does the opposite of what ALL([Column]) does. It instructs CALCULATE to remove filters from every column in a table except for the ones you specify.

The formula CALCULATE( [Total Sales], ALLEXCEPT(Sales, Sales[Year]) ) means: "Calculate the total sales, keeping the filter on the Year column but removing every other filter from the Sales table." This is a useful shortcut if you need to preserve just one or two filters from a table with many columns.

Tips for Using ALL Effectively

• Think in Context: Before writing any DAX, always ask yourself: "What is the current filter context, and which parts of it do I need to modify or remove?"
• Use Variables for Clarity: For more complex measures, use variables (VAR) to break your logic into readable steps. This makes your DAX much easier to write, debug, and understand later.

% of Total Sales =
    VAR __CurrentCategorySales = [Total Sales]
    VAR __AllSalesTotal = CALCULATE( [Total Sales], ALL(Sales) )
    RETURN
        DIVIDE( __CurrentCategorySales, __AllSalesTotal )

• Start Broad, Then Refine: When first learning, it's fine to start with ALL(TableName). Once you see it working, think about whether you need to refine it to ALL(TableName[ColumnName]) to make it more interactive with slicers.
• ALL Is for Calculation Modifiers: Remember that ALL is almost always used inside CALCULATE. This function combination is the foundation of powerful data modeling in Power BI.

Final Thoughts

The ALL function is a fundamental concept in DAX that opens up a world of advanced analytics. By allowing you to step outside the default filter context, it enables you to create meaningful comparisons, financial ratios, and dynamic period-over-period reports. Mastering ALL is a major step toward becoming proficient in Power BI.

While mastering DAX functions like ALL is rewarding for building reports from scratch, sometimes you need answers faster without having to wrestle with complex filter contexts. We built Graphed for this exact reason. You can simply ask our AI-powered analyst, "Show me sales by product category as a percentage of total sales for last quarter," and we handle connecting to your data and generating the live, interactive report for you in seconds.