DAX Measure: How to Subtract Two Columns

Yes. A DAX measure can subtract two columns, but it usually must aggregate each column first because a measure needs one result in the current filter context. For example, create Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost]) to subtract total costs from total revenue; use SUMX instead when the calculation must happen separately on each row before the results are added.

The basic subtraction measure

In Power BI Desktop, select the table where you want to store the measure, then choose Modeling > New measure (or right-click a table in the Data pane and choose New measure). Enter a measure name, an equals sign, and the DAX expression. Assuming a table named Sales has numeric columns named Revenue and Cost, the measure is:

Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost])

In a visual, add Profit alongside fields such as product, month, or region. The measure calculates the difference between the revenue total and cost total for whichever product, month, region, slicers, and filters currently apply. For example, if the filtered rows total 1,000 in Revenue and 650 in Cost, Profit returns 350.

The table and column names in your expression must match the model. If a name contains spaces, DAX still accepts it inside the square-bracket reference, as in SUM('Sales Data'[Net Revenue]). The single quotes around the table name are commonly used when the name includes spaces or special characters.

Why measures need aggregation

A column contains many values, while a measure returns one value for the current evaluation. If you write Profit = Sales[Revenue] - Sales[Cost] as a measure, Power BI may report that it cannot determine a single value for a column. A visual grouped by product could have many Sales rows for one product, so there is no unique Revenue or Cost value for DAX to subtract.

Functions such as SUM, AVERAGE, MIN, and MAX turn a column’s values into a single result. Choose the aggregation that matches what the metric means. For financial totals, SUM is usually appropriate; for a per-unit comparison, an average or a ratio may be more meaningful. The right operation depends on the data and the question, not just on making the error disappear.

Measures are evaluated when a visual or formula requests them. They do not store a separate result for every row in the source table. This is why the same measure can show a different value on a card, on a product row, and in a monthly chart: each location may have a different filter context.

Subtracting row by row with SUMX

There is an important difference between subtracting two totals and subtracting values on each row. If the calculation is “revenue minus cost for every transaction, then add those transaction profits,” use the iterator SUMX. Assuming Sales contains one transaction per row and the same Revenue and Cost columns, write:

Profit by transaction = SUMX(Sales, Sales[Revenue] - Sales[Cost])

SUMX evaluates the expression for each row of Sales in the current filter context, then adds those results. For a simple subtraction of additive columns, this will generally give the same total as subtracting the two sums. For example, rows with revenue and cost of 100 and 60, and 200 and 130, produce row profits of 40 and 70, for a total of 110. The total-revenue-minus-total-cost calculation is also 300 minus 190, or 110.

That equality does not mean the two patterns always represent the same business logic. You may need SUMX when each row has a specific rule, such as applying a row-level adjustment before summing. Conversely, if your metric is simply overall revenue less overall cost, two SUM expressions are direct and easy to read. SUMX iterates over rows, so using it over an unnecessarily large table can also add work; use the simplest expression that correctly represents the calculation.

How filter context changes the result

A measure responds to filters supplied by the report. If a chart groups Profit by month, Power BI evaluates the expression separately for each month. A slicer for a product category narrows the rows used in each evaluation. You normally do not need to write separate formulas for each category or month; the model’s filter context provides those boundaries.

Totals in a matrix are not necessarily calculated by adding the numbers displayed in its rows. DAX reevaluates a measure in the total’s broader filter context. For the basic subtraction of sums, that total is revenue for all included rows minus cost for all included rows. With percentages, averages, or more complex row logic, a grand total may therefore differ from the sum or average of the visible row results. That behavior is usually intentional, but it is worth checking if a total seems surprising.

If a measure should ignore some filters or apply a particular condition, DAX can modify filter context with functions such as CALCULATE. Do not add CALCULATE just to subtract columns: the basic SUM-minus-SUM expression already respects the current context. Use a filter modifier only when the definition of the metric calls for different filtering behavior, and validate the result against a small, known example.

Subtracting values from different tables

You can subtract measures based on columns in different tables. For instance, if Sales contains revenue and Expenses contains cost, and both tables are correctly related to shared dimensions such as Date and Department, define a measure like:

Net Amount = SUM(Sales[Revenue]) - SUM(Expenses[Cost])

This works when the model’s relationships allow the relevant filters to reach both tables. If a date selection filters Sales but not Expenses, the result may compare selected revenue with unfiltered expenses. A repeated or unexpected value can therefore be a modeling issue rather than a subtraction issue. Check the relationship paths, relationship direction, and whether both fact tables connect appropriately to shared dimension tables.

Also check the grain of each table. Sales might store one row per transaction line, while Expenses might store one row per monthly department total. Aggregating each table can still be valid, but only if the filters and definitions make the two totals comparable. A measure cannot repair mismatched business definitions, duplicate records, or missing relationships.

Measure or calculated column?

A measure is usually the right choice when the answer should change with slicers, report filters, or visual groupings. It calculates on demand and does not add a stored result for every source row. Profit, remaining balance, and total variance are common examples, provided the required aggregation is clear.

A calculated column is evaluated row by row when the model is processed or refreshed. If you need a persistent value for each Sales row—for example, a transaction-level margin that users can place in a slicer or use as a category—a calculated column may be appropriate. Assuming the Revenue and Cost columns are in Sales, the DAX expression is:

Sales[Row Profit] = Sales[Revenue] - Sales[Cost]

Create it with Modeling > New column. A calculated column can be summed later, but it uses model storage and does not recalculate its stored row values in response to report slicers. A measure responds to filter context but is not a row-by-row stored field. Choose based on whether you need a dynamic aggregate or a value attached to each record.

Common problems and checks

A single-value error: A measure references raw columns without reducing them to one value. Use suitable aggregations, or use SUMX if the expression belongs at row level.
Wrong aggregation: SUM may be inappropriate for a unit price, percentage, or already-aggregated value. Confirm what each row represents and select an aggregation that matches the metric.
Unexpected totals: The measure is reevaluated in the total context. Compare its definition with the intended total rather than assuming it should add displayed rows.
Unexpected results across tables: Check relationships, filter propagation, table grain, and duplicate data. A mathematically valid subtraction can still compare incompatible totals.
Text or incompatible data: Ensure both inputs are numeric. Text values that look like numbers should be converted to an appropriate numeric data type in the model or during data preparation.
Blank values: Decide how missing revenue or cost should be treated. DAX arithmetic and aggregation behavior around blanks can affect what appears in a visual, so test records with missing inputs rather than assuming blanks mean zero in every business sense.

For troubleshooting, put the two component measures in a table visual beside the grouping field, then add the subtraction measure. This makes it easier to see whether the problem comes from one input, a filter, or the subtraction itself. Test a small set of records with hand-calculated expected totals before relying on a measure in a report.

Choosing the right pattern

Use this quick guide to select a pattern:

Overall total A minus overall total B: subtract two aggregated expressions, such as SUM of revenue minus SUM of cost.
Calculate a difference on every row, then total it: use SUMX over the table at the correct row grain.
Store a result on each record: consider a calculated column, especially when the row result is needed for grouping or filtering.
Compare totals from separate tables: aggregate each table and verify that relationships and filters make the results comparable.

There is no universal formula that is correct for every pair of columns. Start by stating the business rule in words—such as “total billed amount minus total refunds” or “sum of each line’s quantity times its unit margin”—then choose an aggregation and table grain that implement that rule.

Frequently Asked Questions

Can I subtract two columns directly in a DAX measure?
Usually not as bare column references when each column has multiple values in the current context. Aggregate them, for example with SUM, or iterate rows with SUMX if subtraction must happen per row.

Should I use SUM or SUMX to subtract columns?
Use SUM of each column when the definition is the difference between their totals. Use SUMX when the required calculation is a row-level difference that is then summed, or when each row needs additional logic.

Why does my DAX measure say it cannot determine a single value?
The expression likely asks for a column value where multiple rows are in context. A measure needs a scalar result; aggregate the column, use an iterator such as SUMX, or use a calculated column if the calculation is genuinely row-specific.

Can a measure subtract columns from different tables?
Yes. Aggregate the relevant columns in each table and subtract the resulting measures or expressions. Confirm that model relationships let report filters reach both tables and that the values share compatible definitions and grain.

Why is the total different from adding the rows in my visual?
A measure is recalculated for the total’s filter context rather than automatically adding displayed results. For a difference of additive totals, the total is the difference between the overall totals; more complex calculations can produce other valid totals.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.