How to Ignore Filter in Measure Power BI

Building a Power BI report often involves slicing and dicing your data, but sometimes you need a calculation to stand firm, completely unaffected by slicers or other filters on the page. You might need a KPI card that always shows the grand total, or you might want to calculate what percentage a specific product makes up of your entire sales volume. In these scenarios, you need to tell your DAX measure to ignore a filter. This article will show you exactly how to do that using a few powerful DAX functions.

Why Would You Want to Ignore a Filter?

Before diving into the "how," let's quickly cover the "why." Ignoring filters isn't an obscure trick, it's a fundamental technique for adding powerful context to your reports. When your visualizations can compare a filtered subset of data against the whole, you unlock much deeper insights.

Here are a few common scenarios where this is essential:

• Calculating Percent of Total: This is the classic example. If you have a bar chart showing sales by product category, you might want to show what percentage each category contributes to the total sales of the entire company, not just the categories currently visible in the chart. To do this, the denominator in your percentage calculation (Total Company Sales) must ignore the product category filter.
• Benchmarking and Variance Analysis: You might want to compare a specific region's performance against the company-wide average. The regional slicer would filter for that region's data, but the company-wide average measure needs to ignore the slicer to provide a stable benchmark.
• Fixed KPI Cards: A dashboard usually includes key performance indicators (KPIs) in "cards" — visuals that show single, important numbers like Total Revenue or Total Customers. Business leaders often want these headline numbers to remain constant, showing the overall picture regardless of how they filter the other visuals on the page.

The Core DAX Functions for Manipulating Filters

The magic behind ignoring filters lies in a group of DAX functions that modify something called the "filter context." At the center of it all is the CALCULATE function. Think of CALCULATE as the command center for your measures — it lets you perform an expression within a modified environment where you control the filters.

The basic syntax is:

CALCULATE(<expression>, <filter1>, <filter2>, ...)

The first argument is the calculation you want to perform (e.g., SUM(Sales[SalesAmount])). The subsequent arguments are where you define how the filter context should be changed. Let's look at the functions you'll use in that filter section.

1. Using ALL() to Remove All Filters

The simplest and most common function for this job is ALL(). As its name suggests, ALL() removes filters from a specified table or column, effectively giving you access to the entire dataset for your calculation.

Let's walk through an example. Imagine you have a simple Sales table with columns like Product Category and Sales Amount. First, you'd create a basic measure for total sales:

Total Sales = SUM('Sales'[SalesAmount])

If you put this measure in a table visual with Product Category, it shows the sales for each category because it operates within the filter context of each row.

Now, let's create a measure to calculate the grand total of all sales, ignoring any filters. This will be the denominator in our percentage calculation:

All Sales = CALCULATE([Total Sales], ALL('Sales'))

Here’s the breakdown:

• CALCULATE([Total Sales], ...): We're telling Power BI to calculate our [Total Sales] measure, but with a modification.
• ALL('Sales'): This is the modification. It instructs CALCULATE to remove all filters from the 'Sales' table before performing the sum.

When you add this "All Sales" measure to your table visual, you'll see the same grand total amount repeated for every single category. This is exactly what we want!

Now, you can easily create a "Percent of Total" measure:

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

(Pro Tip: Always use the DIVIDE() function for division in DAX. It automatically handles division-by-zero errors without you needing to write a more complex IF statement.)

When you format this measure as a percentage and add it to your table, each category will show its contribution to the absolute grand total. Even if you add a slicer for product category and select only a few, the percentage still correctly reflects their share of the entire company's sales, because the [All Sales] measure is completely ignoring the context from both the slicer and the table rows.

2. Using ALLSELECTED() to Respect Slicers

What if you want to calculate a percentage based on what the user has selected, rather than the absolute overall total? For example, if a user filters the report to see only "Clothing" and "Accessories," you want to know what percentage of that selection's total each category represents.

This is where ALLSELECTED() comes in. It's a subtle but powerful variation of ALL():

• ALL() ignores filters from everywhere — both slicers/external filters and the internal filter context of the visual itself.
• ALLSELECTED() respects external filters (like slicers) but ignores the internal filter context of the visual.

Let's create a new measure:

All Selected Sales = CALCULATE([Total Sales], ALLSELECTED('Sales'))

And now, a new percentage measure:

% of Selected Total = DIVIDE([Total Sales], [All Selected Sales])

If you build a report with a slicer for Product Category and a table showing both percentage measures, you can see the difference clearly. With nothing selected in the slicer, both percentage columns will show identical values. But as soon as you select a few categories in the slicer, the % of Selected Total will recalculate to show you the breakdown within your selection, while % of Total Sales continues to show the percentages relative to the grand total.

This is incredibly useful for giving users the ability to analyze a specific subset of data in detail.

3. Using ALLEXCEPT() to Keep Select Filters

Sometimes you need to ignore filters on a table, but not all of them. You might want to remove most filters while preserving the filter from one or two specific columns. For this, you use ALLEXCEPT().

The syntax is: ALLEXCEPT(<table>, <column1>, <column2>, ...).

Imagine you want to calculate the total sales for a specific fiscal year, and you need this calculation to ignore filters from products, regions, and everything else — except for the year. The year filter must be preserved.

Here's how you'd write that measure:

Yearly Total Sales = CALCULATE([Total Sales], ALLEXCEPT('Sales', 'Calendar'[Year]))

This measure, when used in visuals, would always show the total for the year being evaluated in that context, regardless of what other slicers are active. It provides a stable total against which you can compare different products or regions within that year.

Best Practices and Common Pitfalls

As you start using these functions, keep a few key points in mind to avoid common frustrations.

Be Specific With Your Filters

Using ALL('Sales') removes all filters on the entire Sales table. But you can also be more precise and write ALL('Sales'[Product Category]). This removes the filter only from the Product Category column, leaving filters on other columns (like Region or Date) intact. Being surgical with your filter removal can prevent unexpected results, especially in complex data models.

Understanding Filter Context vs. Row Context

DAX can be tricky because it operates in different "contexts." The ALL family of functions works on the filter context — the set of active filters being applied to the data model from slicers, visuals, or other measures. This is different from the row context, which is when a calculation iterates through a table row by row. These functions are your tools for changing the filter context to get the result you need.

Test Your Measures Incrementally

Don’t try to write a complex, multi-layered DAX formula all at once. Build it up in steps. First, create your base measure (e.g., [Total Sales]). Then, create the denominator (e.g., [All Sales]) and put it into a card or table visual to make sure it’s behaving as expected. Once you've confirmed that part is working, you can combine them into the final percentage measure.

Final Thoughts

Mastering how to ignore filters in Power BI transforms your reports from simple data displays into dynamic analytical tools. By using CALCULATE along with ALL, ALLSELECTED, and ALLEXCEPT, you can create measures that provide essential context, enabling meaningful comparisons like calculating percentages, variances, and stable benchmarks for any KPI.

Writing complex DAX and figuring out filter contexts can definitely feel like a steep learning curve. At Graphed, we've designed our entire platform around eliminating this friction. Instead of manually wrangling data models and debugging formulas, you can simply connect your data sources (like Google Analytics, Salesforce, or Shopify) and ask for what you need in plain English. Want a report showing "sales by campaign as a% of total revenue this quarter"? Just ask, and we’ll instantly generate a real-time, interactive dashboard for you. Sign up for a free account with Graphed and see how easy data analysis can be.