How to Calculate Sum with Filter in Power BI

Doing a simple sum in Power BI is easy, but what if you need to calculate the sum for just a specific category, region, or time period? This is where you move beyond drag-and-drop and into the real power of Power BI: DAX formulas. This article will walk you through how to calculate a sum with a specific filter using the two most important functions you’ll ever learn in DAX: CALCULATE and SUM.

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

First, Why DAX Measures are Essential

When you drag a field like "Sales" into a Power BI visual, it automatically creates an implicit measure, usually a sum. This is great for getting a quick grand total. However, this simple total reacts to whatever filters are on the page (like from slicers or other charts). You often need more control to ask deeper questions, such as:

• What were our total sales for just the "Electronics" category?
• How much revenue did we generate in the second quarter (Q2)?
• What percentage of our total sales comes from the "North" region?

To answer these questions, you need to create your own calculations. In Power BI, these are called explicit measures, and you write them using DAX (Data Analysis Expressions). This sounds intimidating, but it's just a way to give Power BI specific instructions for your calculations.

The Two DAX Functions You Need to Know

To sum your data with a filter, you need to combine two key functions. Think of them as a team: one does the math, and the other tells it what data to use for the math.

1. SUM(): The Basic Calculator

This one is straightforward. The SUM() function adds up all the numbers in a column. Its structure, or syntax, is super simple:

SUM(<columnName>)

For example, if you have a table named 'SalesData' with a column called 'Revenue', the formula SUM(SalesData[Revenue]) would add up every value in that 'Revenue' column.

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

2. CALCULATE(): The Superstar Function

If you learn only one function in DAX, make it CALCULATE(). This function is a powerhouse that lets you modify the "filter context" of a calculation. In simple terms, it lets you tell your formula: "Do this calculation, but change, add, or ignore the current filters."

The syntax looks like this:

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

• <expression>: This is the calculation you want to perform. For our purposes, this will be our SUM() function.
• <filter1>, <filter2>, ...: This is where the magic happens. Here you define the specific conditions or filters you want to apply before the calculation runs.

When you put SUM inside of CALCULATE, you're telling Power BI: "Sum this column, but only for the rows that meet these specific filter conditions."

How to Create Your First Filtered Sum Measure: Step-by-Step

Let's use a common business scenario. Imagine you have a 'Sales' table with columns like Product Category, Region, and Sales Amount. Our goal is to create a measure that always shows the total sales for the "Apparel" category, regardless of what other filters are active on the report.

Step 1: Create a New Measure

First, ensure you're in the "Report" view in Power BI. Right-click on your 'Sales' table in the Data pane on the right-hand side. Select New Measure from the context menu.

This will open up the formula bar at the top of the report canvas, where you'll write your DAX formula.

Step 2: Write the DAX Formula

In the formula bar, Power BI has likely given your measure a default name like "Measure =". Let's replace that with something descriptive and write our formula:

Apparel Sales = CALCULATE( SUM('Sales'[Sales Amount]), 'Sales'[Product Category] = "Apparel" )

Let’s break down what this does:

• Apparel Sales =: This is simply the name we're giving our new measure.
• CALCULATE( ... ): We're telling Power BI we need to modify the context of a calculation.
• SUM('Sales'[Sales Amount]): This is our expression — the thing we want to calculate. We’re asking to sum the 'Sales Amount' column.
• 'Sales'[Product Category] = "Apparel": This is our filter. We're telling CALCULATE to only include rows where the value in the 'Product Category' column is exactly "Apparel".

After typing the formula, press Enter to save it. You'll see your new measure appear in the 'Sales' table in the Data pane, usually with a small calculator icon next to it.

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

Step 3: Use Your New Measure in a Visual

Now you can use this measure just like any other field. For a simple display, let’s add a Card visual to our report canvas.

1. Click on the Card visual icon in the Visualizations pane.
2. Drag your new [Apparel Sales] measure into the "Fields" area for that visual.

The card will now display the total sales exclusively from the "Apparel" category. If you add a slicer for 'Region' and select "North," your card will still show total apparel sales across all regions, because your DAX formula has hard-coded the filter you wanted.

Level Up: Advanced Filtering Techniques

The example above is a basic start. The true flexibility of CALCULATE shines when you start introducing more complex filtering scenarios.

Adding Multiple Filters

What if you want to find the sales for "Apparel" but only in the "West" region? You can simply add another filter argument to your CALCULATE function. Each filter is separated by a comma.

West Apparel Sales = 
CALCULATE(
    SUM('Sales'[Sales Amount]),
    'Sales'[Product Category] = "Apparel",
    'Sales'[Region] = "West"
)

Power BI will now only sum the sales for rows where both of these conditions are true.

Using the FILTER Function

Sometimes your filter logic is more complex than a simple "column = value" check. For example, maybe you want to sum sales for high-value orders only, say orders over $500. For this, you need the FILTER function inside CALCULATE. The FILTER function iterates through a table row-by-row and evaluates a logical test.

High-Value Sales = 
CALCULATE(
    SUM('Sales'[Sales Amount]),
    FILTER(
        'Sales',
        'Sales'[Sales Amount] > 500
    )
)

Here, the FILTER function goes through the entire 'Sales' table and creates a temporary, filtered version of it containing only the rows where Sales Amount is greater than 500. Then, CALCULATE performs the SUM on that temporary table.

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

Ignoring Page Filters with ALL

A very powerful use case for CALCULATE is when you want to calculate a total that ignores whatever slicers or filters a user has selected. This is essential for calculating things like percentages of grand totals.

Let’s say you have a table showing sales by product category, and you want to add a column showing what percentage each category contributes to the total sales. Your SUM('Sales'[Sales Amount]) measure will get filtered by the table context. To get the grand total, you need to remove the category filter. This is where the ALL function comes in.

First, create a measure to calculate the total sales across all categories:

All Category Sales = CALCULATE( SUM('Sales'[Sales Amount]), ALL('Sales') )

The ALL('Sales') function tells CALCULATE to remove any and all filters applied to the 'Sales' table before summing.

Next, you can create a percentage measure:

% of Total Sales = DIVIDE( SUM('Sales'[Sales Amount]), [All Category Sales] )

Now, when you add this measure to your table visual, it will correctly show the percentage for each category because it divides the sales for that category by the immovable grand total.

Final Thoughts

Understanding how to use CALCULATE to filter a SUM is a fundamental milestone in your Power BI learning. It elevates your reports from static displays of data to dynamic, analytical tools that can answer specific and nuanced business questions, providing insights that simply aren't possible with basic aggregations.

Mastering DAX requires practice, and the learning curve can feel steep, especially when formulas get lengthy. At Graphed we built a tool to shortcut that process. Instead of writing formulas, you can connect your data sources and simply ask in plain English: "Show me a chart of our total sales for Apparel in the West region." We instantly generate the dashboard and visuals for you, pulling live data in seconds. It bridges the gap between knowing what question to ask and knowing how to write the code to answer it.