How to Use ALL Function in Power BI

If you've ever tried to calculate a 'percentage of total' in Power BI, you've likely run into a frustrating problem: as soon as you use a filter or a table visual, your "total" changes! You can’t divide a category's sales by the grand total if the grand total shrinks to just that category's sales. This is where the DAX ALL function comes in as a reporting superhero. This article will show you exactly what the ALL function does, how to use it for common calculations like percent of total, and how it differs from similar functions.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is the ALL Function in Power BI?

At its core, the ALL function is a table function in DAX that returns all the rows in a table or all the values from one or more columns, completely ignoring any filters that may have been applied.

Think of it as a reset button for your data context within a specific calculation. While a visual on your report might be filtered down to "Q1 2024" and "East Region," a measure using ALL can reach back into your original, unfiltered dataset to grab the grand total of everything.

The syntax is straightforward:

ALL( [&lt,tableNameOrColumnName&gt,[, &lt,columnName&gt,[, &lt,columnName&gt,[,…]]]] )

You can give it either a single table name, like ALL(Sales), which will return the entire sales table with no filters, or you can provide one or more specific column names, like ALL(Products[Category]), which returns a distinct list of all product categories, ignoring any filters.

The #1 Use Case: Calculating Percent of Grand Total

The most common and valuable use for ALL is calculating a percentage of a grand total. Let's walk through a practical example step-by-step.

Imagine you have a sales table with columns for Product Category and Revenue. You want to create a table visual that shows the total sales for each category and what percentage of the absolute grand total that revenue represents.

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 Revenue

First, we need a simple measure to sum up our revenue. This is a best practice instead of just dragging the raw 'Revenue' field into your visuals.

In the DAX formula bar, create a new measure:

Total Revenue = SUM(Sales[Revenue])

If you put this into a table visual right now with 'Product Category', Power BI will automatically calculate the sum of revenue for each category - so far, so good.

Step 2: Create a Measure for Grand Total Revenue using ALL

Now, we need a measure that calculates the grand total of all revenue, regardless of which product category is on that row of the table. This is the key step.

Create a second measure:

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

Let's break this down:

• CALCULATE: This is the most powerful function in DAX. It modifies the filter context of a calculation.
• [Total Revenue]: This is the expression we want to calculate.
• ALL(Sales): This is the filter modifier. We are telling CALCULATE to perform the sum after removing all existing filters from the 'Sales' table.

Now, if you add this Grand Total Revenue measure to your table, you'll see the same total value repeated for every single category. That's exactly what we want!

Step 3: Create the Final Percentage Measure

The last step is simple division. We divide the individual category's revenue by the unchanging grand total.

Create a third measure:

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

Using the DIVIDE function is safer than using the slash operator (/) because it gracefully handles division-by-zero errors without breaking your visual (it will just return blank). Make sure to format this measure as a percentage by selecting it in the Fields pane and clicking the '%' icon in the 'Measure tools' tab on the ribbon.

When you drop this final measure into your visual, you'll have a clear, accurate breakdown of each category's contribution to your total revenue.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Going Deeper: Using ALL on Specific Columns

So far, we've used ALL(TableName), which removes all filters from the entire table. But what if you want to remove filters from just a single column while respecting others?

This is where ALL(TableName[ColumnName]) comes in handy. It ignores filters applied to the specified column but keeps any other filters active (e.g., from date slicers or other category columns).

Example: Percent of Regional Sales

Let's say your 'Sales' table also contains a 'Region' column. You want to see each product category's revenue as a percentage of its region's total sales, not the overall grand total.

If you filter your report for the "West" region, the baseline total should be the total for the "West" region, not the entire company.

For this, you would create a measure like this:

Revenue for Current Context = CALCULATE([Total Revenue], ALL(Sales[Product Category]))

This measure removes any filter coming from the 'Product Category' column but will respect any filters on the 'Region' column. Now, you can create a percentage measure:

% of Regional Revenue = DIVIDE([Total Revenue], [Revenue for Current Context])

When you build a visual with 'Region', 'Product Category', and this new measure, you'll see how each category contributes to its respective regional total. Filtering your page by "West" will show percentages that add up to 100% for the West region products, which is a far more useful view for regional analysis.

ALL vs. Similar DAX Functions: ALLEXCEPT and ALLSELECTED

New Power BI users often get confused between ALL, ALLEXCEPT, and ALLSELECTED. Understanding the difference will vastly improve your reporting capabilities.

ALL vs. ALLEXCEPT

While ALL removes all filters, ALLEXCEPT removes all filters from a table except for the ones on specific columns you designate. It's essentially the inverse of a single-column ALL.

• ALL(Sales[Product Category]) says: Keep all filters, but ignore the one on Product Category.
• ALLEXCEPT(Sales, Sales[Region]) says: Ignore all filters, but keep the one on Region.

In many simple cases, these can achieve the same result via different logic. The ALLEXCEPT function becomes more useful when you have many interconnected filters and you want to preserve just one or two in a calculation.

Graphed

Still Building Reports Manually?

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

Watch the Demo

ALL vs. ALLSELECTED

This distinction is crucial and causes the most confusion.

• ALL(TableName): Ignores all filters on the table, no matter where they come from (slicers, other visuals, the table itself). It always returns the dataset's absolute grand total.
• ALLSELECTED(TableName): Ignores the filter context from within the current visual but respects filters coming from outside the current visual (like page slicers or filters from other visuals).

Let's make this real. Imagine you have a slicer on your report page for the Year. Your visual is a table showing sales by Product Category.

• A measure using CALCULATE([Total Revenue], ALL(Sales)) will show the grand total revenue from all years in your dataset, regardless of what year you select in the slicer.
• A measure using CALCULATE([Total Revenue], ALLSELECTED(Sales)) will show the total revenue for only the year(s) selected in the slicer.

ALLSELECTED is perfect when you want to show "percent of the visible total," which changes based on a user's slicer selection. ALL is for when you need to show "percent of the all-time, absolute total," which never changes.

Final Thoughts

The ALL function is one of the foundational building blocks of powerful DAX calculations. It gives you control over the filter context, allowing you to create stable denominators for percentage calculations and compare granular data against a meaningful benchmark. Once you master how ALL cooperates with CALCULATE and understand its relationship to ALLEXCEPT and ALLSELECTED, you'll be able to build much more sophisticated and insightful reports in Power BI.

While learning DAX is rewarding, we know that there's often a steep learning curve involved in becoming proficient with traditional business intelligence tools. We built Graphed because we believe getting insights from your data shouldn't require hours of technical tutorials or manual report wrangling. You can simply connect data sources like Google Analytics, Shopify, and Salesforce, and then ask for the dashboards and reports you need in plain English - no complex DAX formulas required. We turn hours of analytical work into seconds, so you can get a clear view of your business performance and get back to making decisions.