Power Pivot SUM Function: Syntax, Examples, and Tips

In Power Pivot, the DAX SUM function adds the numeric values in one column, returning a total that responds to the filters in a PivotTable. Its basic syntax is SUM(TableName[ColumnName]); for example, a measure written as =SUM(Sales[Amount]) totals the Amount column in the Sales table.

What the Power Pivot SUM function does

Power Pivot uses Data Analysis Expressions, usually called DAX, for measures and calculated columns in the Excel Data Model. DAX SUM aggregates one column from a model table. It returns a single numeric result rather than a row-by-row list, which makes it a common choice for totals in PivotTables and reports.

The important distinction is that a measure is evaluated in the current filter context. If a PivotTable shows sales by region, the same measure can return the total for each region and a grand total. A slicer, report filter, or row and column label can change the records included in the calculation without changing the measure itself.

For instance, if the Sales table has an Amount column containing 100, 250, 400, and 500, the total with no filters is 1,250. If a filter leaves only the rows with 100 and 400, the measure returns 500. The function is still summing the same column; the model’s current context determines which rows contribute.

SUM syntax and requirements

The DAX syntax is SUM(<column>). Supply a single column reference, usually written as TableName[ColumnName]. In a measure definition, the equals sign starts the expression, as in =SUM(Sales[Amount]). These are DAX expressions for the Power Pivot model, not worksheet formulas to enter in a cell.

One column: SUM accepts one column argument, not a range of separate columns or an arithmetic expression.
Numeric data: The chosen column should contain numbers that can be added. Set the column’s data type correctly in the model, especially if values came from an imported text file.
A table reference: Use the model table and column names, not a worksheet address such as B2:B100.
Filter-aware result: A measure generally returns a different total when PivotTable fields, slicers, or filters change the context.

SUM adds numeric values and does not treat blank cells as zero-valued records that change the total. A numeric zero contributes zero. If the source contains text in a column intended to be numeric, fix the data type or clean the source rather than expecting SUM to interpret arbitrary text as an amount.

Create a SUM measure in Excel

In Excel for Microsoft 365 on Windows, first load the data into the Data Model. When importing through Power Query, select the option to add the data to the Data Model, or use the relevant Power Pivot data-loading options. The Power Pivot add-in must be available and enabled; it is a Windows feature and is not fully available for authoring and managing models in Excel for Mac.

Open the Power Pivot tab and select Measures > New Measure. If the Power Pivot tab is missing, check whether the add-in is available and enabled in Excel’s add-in settings.
In the New Measure dialog, choose the table where the measure should be stored. This choice organizes the model; it does not limit the measure to only that table’s rows.
Enter a descriptive measure name, such as Total Sales, and enter the DAX expression =SUM(Sales[Amount]), assuming the model table is named Sales and its numeric column is Amount.
Choose an appropriate number format, such as currency for a sales amount, then confirm the measure.
Insert or select a PivotTable based on the Data Model. Add the new measure to the Values area, and add fields such as Region or Product to Rows, Columns, or Filters.

The measure is stored in the workbook’s Data Model, not in an ordinary worksheet cell. It can be reused in multiple PivotTables that use that model. In the PivotTable field list, measures are generally distinguishable from regular columns by their calculation or measure indicator.

How filter context changes the total

Filter context is the set of filters active when a DAX expression is evaluated. A PivotTable row label creates a different context for each row. A slicer selection narrows the context for the report, and a report filter can narrow it further. A SUM measure automatically evaluates within that context, so a single definition can provide subtotals and a grand total.

Suppose Sales contains Amount and Region columns. Put Region in the PivotTable rows and the Total Sales measure in Values. Each region’s result includes only Sales records associated with that region. If you select one year in a slicer, the displayed regional totals are also limited to that year, provided the model’s date table and relationships are set up to filter Sales correctly.

A common misconception is that SUM itself knows what a region or year means. It does not; those fields affect the result because the model applies filters to the relevant table. If a slicer appears to have no effect, check the relationships between tables, whether the slicer field belongs to a related table, and whether the PivotTable is using the intended Data Model.

SUM compared with SUMX

Use SUM when the values to add already exist in one column. It is direct and readable for totals such as revenue, units, or recorded costs. It does not accept an expression like quantity multiplied by unit price as its argument; that is a row-by-row calculation, so SUMX is the relevant DAX function.

SUMX is an iterator: it evaluates an expression for each row of a table and then adds the results. For example, if Sales has Quantity and UnitPrice columns and there is no stored line-total column, a measure can use =SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]). The function multiplies quantity by price for each Sales row, then sums those row results within the current filter context.

If the table already has a reliable LineAmount column, a simpler measure is =SUM(Sales[LineAmount]). That avoids repeating the multiplication in every report calculation. Whether to store a calculated value or calculate it in a measure depends on the model’s design, data volume, and reporting needs; the key point is to match SUM to a column and SUMX to a table plus an expression.

Worked example: total sales by product

Assume the model has a Sales table with Product, Region, and Amount columns. The four example Amount values are 100, 250, 400, and 500, for a grand total of 1,250. Create a measure using =SUM(Sales[Amount]) and name it Total Sales. Add Product to PivotTable Rows and Total Sales to Values.

The result shows the amount for each product represented by the underlying rows. If Product A has amounts of 100 and 250, its subtotal is 350. If Product B has 400 and 500, its subtotal is 900. The PivotTable grand total is 1,250 because 350 plus 900 equals 1,250. These subtotals are not separate formulas; the measure is recalculated for each product’s filter context.

Now add Region as a slicer. Selecting one region filters the records before the measure is evaluated, so the product subtotals and grand total change to reflect that selection. If you clear the slicer, the measure returns to the totals for all regions. This is why a measure is usually preferable to a manually calculated total when the same report needs to respond to different groupings and filters.

Common SUM problems and fixes

The measure returns an error: Confirm the table and column names, including spaces and spelling, and make sure the argument is a column reference. If a table name contains spaces, DAX commonly encloses it in single quotation marks, as in =SUM('Sales Data'[Amount]).
The result is zero or unexpectedly small: Inspect the column’s data type and source values. Imported numbers sometimes arrive as text, or the active PivotTable filters may exclude most records. Check the filter buttons and slicers before changing the formula.
The same total repeats on every row: Confirm that the field in the PivotTable row area can filter the table being summed. A missing or inactive relationship can prevent a category from narrowing Sales, making each row show the same total.
The total is larger than expected: Check for duplicate source records and model relationships that create unintended results. Also verify whether the field represents a transaction amount or a repeated summary value; summing a value copied onto every detail row can overstate a total.
A calculated column behaves differently from a measure: A calculated column is evaluated and stored row by row when the model processes data, while a measure is evaluated when a report requests a result. For interactive totals, create a measure rather than placing an aggregate expression in a calculated column.

When troubleshooting, compare the model’s source rows with a small hand-calculated subset. Remove PivotTable filters temporarily, verify the expected unfiltered total, and then restore filters one at a time. This helps separate a data issue from a relationship issue or an incorrect expectation about filter context.

When to use another aggregation

SUM is not the right choice for every numeric question. Use an average when the question is about a typical value, a count when it is about records, or a distinct count when it is about unique entities. Power Pivot exposes these as different aggregations or DAX calculations; they are not interchangeable with a total. For example, adding customer IDs would produce a number but would not tell you how many customers there are.

For a total based on a conditional rule, the measure may need a filter-aware DAX pattern rather than a plain SUM. For example, a report that totals only completed orders requires the model to apply the completed-status condition. The appropriate expression depends on the table structure and desired behavior when users add filters, so define the business rule before choosing a formula.

Also distinguish a Power Pivot measure from the default summarization of a numeric field in a PivotTable. A regular numeric column can often be placed in Values and summarized by Sum, but a named DAX measure is more explicit, reusable, and easier to manage consistently across reports. A measure is especially useful when calculations must respond predictably to slicers or when the model contains related tables.

Frequently Asked Questions

What is the syntax for SUM in Power Pivot?

The DAX syntax is SUM(TableName[ColumnName]). In a measure, enter an expression such as =SUM(Sales[Amount]), using the actual table and numeric column names in your Data Model.

Can Power Pivot SUM add two columns?

No. SUM takes one column argument. To add values calculated from two columns row by row, use SUMX with a table and an expression, or create an appropriate line-amount column and sum that column.

Why does SUM return the same number for every PivotTable row?

The row field may not be filtering the table used by the measure. Check that the tables have a valid relationship and that the PivotTable is based on the Data Model. A missing relationship can leave the measure with the same filter context on every row.

Should I use SUM or SUMX?

Use SUM for a total already stored in one numeric column. Use SUMX when you need to calculate a value for each row, such as quantity multiplied by unit price, and then add those row results.

Can I use Power Pivot on a Mac?

Power Pivot’s full model-authoring tools are designed for Excel on Windows and are not available in the same way in Excel for Mac. If you need to create or edit a Power Pivot model, use a supported Windows version of Excel; capabilities for viewing or working with workbook data on Mac can differ by release.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.