How to Do SUMIF in Power BI

If you're coming to Power BI from Excel, one of the first things you'll probably look for is a familiar function: SUMIF. It’s a spreadsheet workhorse for calculating totals based on a specific condition. However, you’ll quickly discover there’s no SUMIF function in Power BI. This article will explain why that is and, more importantly, how to accomplish the exact same thing using Power BI’s more powerful DAX calculation engine.

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

Why Is There No SUMIF Function in Power BI?

The absence of a direct SUMIF function isn't an oversight, it's by design and highlights a fundamental difference between Excel and Power BI.

Excel operates on a cell-based grid. Functions like SUMIF are designed to look at specific ranges of cells, check for a condition in one range, and sum corresponding cells in another. It's direct, intuitive, and perfect for a spreadsheet environment.

Power BI, on the other hand, operates on a relational data model with columnar databases. Its calculation language, DAX (Data Analysis Expressions), is designed to work with entire columns and tables, not specific cell ranges. This approach is far more scalable and efficient for handling large datasets. Instead of a single SUMIF function, Power BI uses a combination of more versatile functions - chief among them being CALCULATE - to achieve the same result in a way that’s much more flexible.

The Power BI Solution: CALCULATE

The hero of conditional calculations in DAX is the CALCULATE function. Think of it as a supercharged version of SUMIF, SUMIFS, COUNTIF, and more, all rolled into one. It's arguably the most in-demand function in all of DAX.

At its core, CALCULATE does two things:

• It takes a calculation you want to perform (like a SUM).
• It modifies the context of that calculation with filters you provide.

In simple terms, you tell it what to calculate and how to filter the data before calculating. This is the same logic as SUMIF.

The basic syntax looks like this:

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

• Expression: This is the calculation you want to perform, such as SUM([Sales Amount]).
• Filter: These are the conditions you want to apply, equivalent to the criteria in SUMIF.

Let's walk through some practical examples to see how this works.

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

How to Replicate SUMIF: A Step-by-Step Example

Imagine we have a simple sales table named 'Sales' with the following columns: Product, Category, Region, and SalesAmount.

Our goal is to calculate the total sales only for the "Electronics" category.

In Excel, you would write:

=SUMIF(CategoryColumn, "Electronics", SalesAmountColumn)

In Power BI, we'll create a measure to do this. Measures are dynamic calculations that respond to the context of your report (like filters and slicers), which makes them perfect for this task.

Step 1: Create a New Measure

In the Power BI report view, find your 'Sales' table in the Data pane. Right-click on the table name and select New measure.

Step 2: Write the DAX Formula

The formula bar will appear at the top of the report canvas. Here, you'll enter your DAX expression. We want to sum the SalesAmount column, but only where the Category is "Electronics".

Enter the following formula:

Total Electronics Sales = CALCULATE( SUM(Sales[SalesAmount]), Sales[Category] = "Electronics" )

Let's break this down:

• Total Electronics Sales = is just the name of our new measure.
• CALCULATE(...) is the main function we're using.
• SUM(Sales[SalesAmount]) is the expression. We're telling CALCULATE that our ultimate goal is to sum the SalesAmount column from our 'Sales' table.
• Sales[Category] = "Electronics" is the filter. This tells CALCULATE to temporarily filter the entire 'Sales' table to include only rows where the value in the Category column is "Electronics" before it performs the sum.

Hit Enter to save the measure. You'll now see it in your 'Sales' table in the Data pane, marked with a calculator icon.

Step 3: Visualize the Result

You can now use this measure in any visual. A simple way to check your work is to select a Card visual from the Visualizations pane. Then, drag your newly created "Total Electronics Sales" measure into the "Fields" area of the visual. The card will display the calculated total sales for just the electronics category.

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

How to Replicate SUMIFS (Multiple Conditions)

Excel requires a different function, SUMIFS, to handle multiple conditions. With CALCULATE, you simply add more filter arguments.

Let's say we want to find the total sales for the "Electronics" category, but only in the "North" region.

In Excel, this would be:

=SUMIFS(SalesAmountColumn, CategoryColumn, "Electronics", RegionColumn, "North")

In Power BI, it's just as simple. We create another measure:

North Electronics Sales = CALCULATE( SUM(Sales[SalesAmount]), Sales[Category] = "Electronics", Sales[Region] = "North" )

Here, we just added a second filter argument separated by a comma. CALCULATE treats multiple filters as an AND condition, meaning a row must meet all criteria to be included in the calculation. It will first filter the table for "Electronics" and then further filter that result for "North" before summing the remaining SalesAmount values.

Using More Complex Filter Conditions

The filtering logic isn’t limited to "equals". You can easily use other operators like greater than (>), less than (<), or not equal to (<>).

Example: Sales for transactions over $500

Let's calculate the total sales amount only from individual sales that were greater than $500.

Large Transaction Sales = CALCULATE( SUM(Sales[SalesAmount]), Sales[SalesAmount] > 500 )

This measure dynamically filters the table for rows where the SalesAmount is over 500 and then sums them up.

Example: All categories except "Hardware"

Here’s how you’d calculate the total for everything but one category.

Non-Hardware Sales = CALCULATE( SUM(Sales[SalesAmount]), Sales[Category] <> "Hardware" )

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

A Note on Slicers (The Real Power BI Way)

While hard-coding filters like "Electronics" is a direct way to replicate SUMIF, the real power of Power BI comes from dynamic interaction. The CALCULATE function responds to selections made in visuals like Slicers.

Instead of creating a separate measure for each category, you could create a single, simple base measure:

Total Sales = SUM(Sales[SalesAmount])

Then, you could add a Slicer visual to your report canvas and drag the Sales[Category] column into its Field well. When a user selects "Electronics" from the slicer, that applies a filter context to the report. Your basic Total Sales measure will now automatically display the value just for electronics - effectively performing a SUMIF without you having to write any special CALCULATE logic at all. This interactivity is what sets Power BI dashboards apart from static spreadsheets.

Final Thoughts

In summary, while Power BI doesn't have a SUMIF function, it provides a far more powerful and flexible alternative with DAX. By using the CALCULATE function, you can create dynamic calculations for any number of conditions, replicating not just SUMIF and SUMIFS, but opening up a world of more complex business logic that would be hard to manage in an Excel sheet.

While learning DAX is incredibly rewarding, there are moments when you just need an answer without debugging formulas. That's why we built Graphed. We simplify the whole process by letting you connect your data and ask questions like, "Show me a chart of total sales by category only for the North region" in natural language. Graphed generates the dashboard and visualizations for you in seconds, helping you move directly from a question to an insight without getting stuck on the technical steps.