DAX SUMIF: How to Sum Values by Condition

DAX has no standalone SUMIF function. To sum a column when a condition is true, create a measure with CALCULATE and SUM; with a Sales table containing Amount and Region columns, the measure below adds the amounts for West and returns 370 for the example data in this article.

Assuming the Sales table has Region and Amount columns, use this DAX measure:

West Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")

The DAX equivalent of SUMIF

In an Excel worksheet, SUMIF takes a range to inspect, a criterion, and an optional sum range. DAX works differently: it evaluates expressions against model tables and filter context. The usual equivalent is CALCULATE(SUM(...), condition). SUM adds a numeric column, while CALCULATE evaluates that sum after applying a filter.

This pattern is used in Power BI measures and in the Data Model through Power Pivot for Excel. It is not entered into an ordinary worksheet cell as a replacement for Excel’s SUMIF. If you only need a calculation in a worksheet, Excel’s SUMIF function is still available. Use DAX when your calculation belongs in a model and should respond to report filters, slicers, or relationships.

The measure name, such as West Sales, is part of the measure definition. In Power BI, create a new measure in the model and enter the definition in the formula bar. DAX examples here use comma separators; depending on regional settings, an installation may display or require different list separators.

Example data and a basic measure

Assume the Sales table contains these rows. The columns are Region, Category, and Amount:

West, Hardware, 120
East, Hardware, 80
West, Service, 200
North, Hardware, 150
West, Hardware, 50
East, Service, 300

With those columns and values in the Sales table, define the measure below. The three West rows total 120 + 200 + 50, so the result is 370.

West Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")

Unlike a worksheet formula tied to specific cell ranges, this measure refers to model columns. When used in a visual, it can be evaluated for different groups of data. For example, a report can show the measure alongside dates or products, and those other filters can affect the total. That flexibility is one reason DAX measures are useful for reports rather than one-off cell calculations.

Adding more than one condition

For an AND condition, pass multiple filter arguments to CALCULATE. Each argument must be true for a row to contribute. With the example Sales table above, this measure sums Hardware sales in the West region. The matching rows are 120 and 50, so the result is 170.

Assuming Sales has Region, Category, and Amount columns as listed above, define:

West Hardware Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West", Sales[Category] = "Hardware")

For a numeric condition, filter on the value column. In the same data, amounts greater than 100 are 120, 200, 150, and 300. Their sum is 770.

Assuming Sales[Amount] contains the six amounts in the example, define:

Sales Over 100 = CALCULATE(SUM(Sales[Amount]), Sales[Amount] > 100)

These are AND-style filters. If the requirement is an OR condition, such as West or North, use a filter expression that explicitly describes the alternatives rather than assuming separate CALCULATE arguments mean OR. One option is the IN operator. With the same Region and Amount columns, the West and North rows total 120 + 200 + 50 + 150, or 520.

Assuming the example Sales table, define:

West Or North Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] IN {"West", "North"})

How CALCULATE and filter context work

Filter context is the set of filters affecting a calculation at a particular point in a report. It can come from a visual’s row or column, a slicer, a page filter, a relationship to another table, or a filter written in DAX. CALCULATE changes that context for the expression it evaluates.

In the West Sales example, CALCULATE applies a filter to Sales[Region]. Other filters, such as a selected date or product, generally remain in effect. A report showing the measure by year can therefore return West sales for each year, not one unchanging all-time total. This behavior is unlike a fixed worksheet range, where the formula does not automatically respond to a model’s relationships or report selections.

There is an important detail: when CALCULATE applies a filter to a column that is already filtered, its new filter normally replaces the existing filter on that same column. So West Sales can show the West total even on a visual row labeled East, because the measure’s West condition overrides the row’s East filter on Region. Filters on other columns are not automatically removed. Understanding this helps explain why a measure can repeat a value or appear to ignore a visual row.

Choosing between SUM, SUMX, and FILTER

SUM is appropriate when you are adding the values of a single numeric column. If the amount must be calculated from other columns for each row, use SUMX, which iterates a table and adds the result of an expression for each row. For example, suppose Sales contains Quantity and UnitPrice columns, and the intended total is quantity multiplied by unit price on each row. With rows of 2 units at 10 and 3 units at 5, the calculated total is 35.

Assuming Sales contains Quantity and UnitPrice with the two example rows, define:

Calculated Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

SUMX is not simply a more powerful spelling of SUMIF: it performs row-by-row expression evaluation, while the filter condition is still handled by the model context. Use FILTER when the condition needs a table expression or logic that cannot be expressed as a simple CALCULATE filter argument. For ordinary conditions such as Region equals West or Amount greater than 100, a direct filter argument is usually clearer.

Complex filters may need FILTER because Boolean filter arguments in CALCULATE have restrictions. For example, a simple Boolean filter argument cannot directly use a measure, and its column references must follow DAX’s rules for filter expressions. When the condition depends on a measure or more involved row logic, write a table filter deliberately and verify the result in the context where the measure will be used.

Criteria, text matching, and blanks

Excel users often expect SUMIF criteria such as wildcards, criteria strings built with operators, or references to a separate criteria cell. DAX does not use Excel’s SUMIF criteria-string convention. Write a DAX condition as an expression, such as Sales[Amount] > 100, or compare a column with a value. For a dynamic selection, the value can come from a slicer or a related model table, but the measure must be designed to read that selection.

DAX text comparison behavior can depend on the model’s collation, and it is commonly case-insensitive. Do not assume Excel wildcard behavior transfers directly: an asterisk in a DAX text comparison is not automatically the same as a SUMIF wildcard. For substring or pattern matching, choose an appropriate text function or build a filter expression that fits the data and desired matching rule.

Blank values deserve attention too. SUM ignores blanks in the numeric column, but a filter on a text column can include or exclude blank entries depending on the condition. If missing categories should be treated as a named group, clean or transform the data, or handle blanks explicitly in the model. Also check whether a column contains numbers stored as text: a numeric-looking value may not behave like a numeric value in a sum or numeric comparison.

Common mistakes and ways to check a result

A frequent mistake is writing a worksheet-style formula with separate criteria_range and sum_range arguments. DAX does not have that SUMIF signature. Another is creating a calculated column when a measure is needed. A calculated column is evaluated row by row during data refresh and stored in the model; a measure is evaluated when a visual requests a result and responds to its filter context. For totals that should change with slicers, a measure is generally the right starting point.

When a result is unexpected, check the following:

Confirm the measure sums the intended numeric column and filters the intended table column.
Check whether a relationship is active and whether filters can travel from the dimension table to the fact table as expected.
Review slicers, visual rows, and page filters that may be changing the current context.
Check whether a CALCULATE filter is replacing an existing filter on the same column.
Compare a small, manually calculated subset of rows with the measure result.
Verify that the source values are numeric and that blanks or unexpected category spellings are handled appropriately.

A useful debugging approach is to place the relevant category and measure in a simple table visual, then remove slicers or filters one at a time. If a measure returns the same value on every row, determine whether its own CALCULATE filter is overriding the row filter or whether a relationship is missing or inactive. If it returns blank, check for unmatched values, blank data, and relationships before changing the arithmetic.

DAX SUMIF compared with Excel SUMIF

Excel’s SUMIF is usually the simplest choice for a one-off calculation over worksheet ranges. It is easy to read and does not require a data model. A DAX measure is more suitable when totals need to interact with a model containing related tables, report visuals, and slicers. Measures are reusable throughout a Power BI report or Excel Data Model, whereas a worksheet formula is usually anchored to a cell location and range.

The mental translation is useful but not exact: SUMIF’s criteria range corresponds roughly to a column being filtered, and its sum range corresponds roughly to the column passed to SUM. DAX adds model relationships and filter context, so the total can be affected by filters beyond the one written in the measure. It is better to think of a DAX measure as a reusable calculation evaluated within a model than as a direct copy of a worksheet formula.

Frequently Asked Questions

Does DAX have a SUMIF function?
No. The common equivalent is CALCULATE with SUM and a filter condition. DAX also provides SUMX for adding a row-by-row expression, but SUMX is not a function named SUMIF.

How do I sum with two conditions in DAX?
Pass two filter arguments to CALCULATE for an AND condition, such as one filter for Region and another for Category. Both conditions must be satisfied for a row to be included.

Can I use a measure as a SUMIF condition?
Not as a simple Boolean filter argument in the same way as a column comparison. Measure-dependent logic may require a table filter expression, and the result should be checked in the intended filter context.

Why does my DAX sum repeat on every row?
A CALCULATE filter may be replacing the visual’s filter on the same column, or the model may not have the relationship needed to pass the row’s filter to the summed table. Inspect the filters and relationships.

Should I use a measure or a calculated column?
Use a measure for a total that should respond to report filters and slicers. Use a calculated column when you need a stored value for each row, such as a row-level classification, rather than a context-sensitive aggregate.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.