Skip to main content

How to Add Up a Column in Power BI with SUM

· 4 min read
Jagdish Kumawat
Founder @ Dewiride

SUM adds up the numbers in a column. As a measure, it gives the total of whatever a visual or filter has selected: every row, one category, one month. Here's its general form and a total of expense claims.

Syntax​

General form
SUM(<column>)

From Microsoft's SUM function reference:

  • <column>: the column that contains the numbers to sum, written as Table[Column].
  • The result is a decimal number.
  • To sum an expression rather than a column, such as price times quantity, the reference points to SUMX.

The example is a measure on the Expenses table of a semantic model with five approved expense claims, whose Amount column holds 45.50, 120.00, 35.00, 18.20 and 75.00.

Step 1: Create the Measure​

Select the Expenses table and New measure (How to Create a Measure in Power BI in the Browser), and enter:

Expenses → Total amount
Total amount = SUM(Expenses[Amount])
  • Total amount is the measure's name.
  • Expenses[Amount] is the column to add up: the Amount column of the Expenses table.

The formula bar with Total amount = SUM(Expenses[Amount]) committed

Press Enter.

Step 2: Show It in a Report​

On a card (How to Add a Card to a Power BI Report), Total amount shows 293.70, the five amounts added up. The table next to it ends with the same total:

A report with cards Total amount 293.70, Expense count 5, People 4 and Signed in as admin@example.com, a Category slicer, a bar chart of Total amount by Category with Travel 183.70 and Meals 75.00, and a table of five expenses with the total 293.70

The bar chart uses the same measure per category: 183.70 for Travel and 75.00 for Meals. The measure follows whatever the visual groups or filters by.

Common Mistakes​

MistakeWhat happensDo this instead
SUM(Expenses)An error: SUM takes a column, not a tableSUM(Expenses[Amount])
Summing a column stored as textAn error, or nothing to sumMake the column a number in Power Query
SUM(Expenses[Amount] * 2)An error: SUM takes only a columnSUMX(Expenses, Expenses[Amount] * 2)

Troubleshooting​

SymptomLikely causeFix
The total is blankNo rows are selected by the filtersCheck the slicers and filters
The total is too highRows are repeated, for example after expanding a list columnCheck the table's rows in Power Query
Too many decimalsThe measure's format is the defaultSet two decimal places (How to Show a Measure with Two Decimal Places in Power BI)

FAQ​

Why a measure rather than the column's own sum?​

A report can sum a numeric column by itself, but a measure has a name, a format and one definition that every report shares.

What's the difference between SUM and SUMX?​

SUM adds a column; SUMX works out an expression for each row of a table and adds the results.

Conclusion​

SUM(Table[Column]) adds up a column. The example's Total amount = SUM(Expenses[Amount]) showed 293.70 for all five claims, and each category's share on a chart.

Additional Resources​