How to Do SUM in Power BI

Calculating a total is often the first step in any data analysis, and while it seems simple, Power BI offers a few different ways to get it done. Knowing which method to use, and when, can save you a lot of headaches as your reports grow in complexity. This tutorial will walk you through the essential ways to calculate a sum in Power BI, from the quick drag-and-drop method to the more powerful and flexible DAX functions SUM and SUMX.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Simplest Way to Sum: Drag-and-Drop Totals

When you're first exploring a dataset or building a quick report, Power BI's automatic summing feature is your best friend. This is often called an implicit measure, which is just a fancy way of saying Power BI is doing the calculation for you behind the scenes.

Let's say you have a table of sales data with a "Sales Amount" column. Here’s how you can quickly get a total:

1. Select a Visual: From the Visualizations pane, click on a visual. A Card visual is perfect for showing a single, large number like a total. A Table or Matrix is great for seeing totals broken down by category.
2. Drag Your Numeric Field: Find your "Sales Amount" column in the pane (usually on the right-hand side). Click on it and drag it into the "Data" or "Values" field of your selected visual.
3. Power BI Does the Rest: By default, Power BI recognizes that "Sales Amount" contains numbers and automatically applies a summarization. For numbers, the default is almost always "Sum." Your Card visual will now display the total sales, and a Table visual will show individual sales with a grand total at the bottom.

You can verify or change the summarization method by clicking the small downward-facing arrow next to your field in the visual's settings. You'll see options like Sum, Average, Minimum, Maximum, and Count.

When Is This Method Good Enough?

The drag-and-drop method is perfect for:

• Quick Data Exploration: When you first connect to a data source and just want to get a feel for the numbers.
• Simple Dashboards: For basic reports where you just need to display top-level totals without complex follow-up calculations.
• One-off Use Cases: If you only need to use the total in a single chart and won't be referring to it anywhere else.

The Limitations of Drag-and-Drop

While easy, this approach has drawbacks that appear as your reports get more sophisticated. The total you created only exists inside that specific visual. If you want to use the same "Total Sales" calculation in another chart, you have to drag the field in and set it up all over again. More importantly, you can't reference this implicit measure inside other, more complex formulas. This is where DAX comes in.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Level Up Your Analysis: Using DAX to Sum Your Data

DAX, which stands for Data Analysis Expressions, is the formula language used in Power BI. Think of it as Excel formulas on steroids. By writing simple DAX formulas, you can create explicit measures - reusable calculations that live inside your data model, not just a single chart.

A measure is a formula that is calculated on the fly based on the context of your report (like filters or slicers). Creating an explicit measure for "Total Sales" makes it a permanent, reusable part of your dataset.

Creating Your First Sum with the SUM Function

The most fundamental aggregation function in DAX is SUM. It does exactly what you think: it adds up all the numbers in a single column.

Here’s the step-by-step guide to creating a total sales measure:

1. In either the Report, Data, or Model view, find your table in the Data pane on the right.
2. Right-click on the table (e.g., your "Sales" table) and select New Measure. You can also click the "New Measure" button in the ribbon at the top of the screen.
3. The formula bar will appear at the top. This is where you'll write your DAX formula.
4. Type the following formula. You'll notice that Power BI provides helpful auto-complete suggestions as you type:

Total Sales = SUM(Sales[SalesAmount])

Let’s break that down:

• Total Sales: This is the name you are giving your new measure. Choose something clear and descriptive.
• = : The equals sign separates the measure name from the formula.
• SUM: This is the DAX aggregation function you're using.
• (Sales[SalesAmount]): This is the argument for the SUM function. You need to specify a single column to add up. The standard syntax is TableName[ColumnName].

Once you press Enter or click the checkmark, your new measure, distinguished by a calculator icon, will appear in the field list under your Sales table. Now, you can drag "Total Sales" into any visual just like a regular column. Power BI will know exactly how to calculate it.

Benefits of Using an Explicit Measure:

• Reusable: Use the "Total Sales" measure in dozens of charts without recreating the logic.
• Centralized Logic: If you ever need to change how total sales is calculated, you only have to update it in one place (the DAX formula), and every visual using it will update automatically.
• Composable: You can use this measure as a building block for more advanced calculations, like Profit Margin = ([Total Sales] - [Total Cost]) / [Total Sales].

Summing with a Condition: Introducing SUMX

What happens when the column you need to sum doesn't exist yet? A classic example is calculating total revenue from a table that only contains "Order Quantity" and "Unit Price" columns.

You might be tempted to create two SUM measures, TotalQuantity = SUM(Sales[OrderQuantity]) and TotalUnitPrice = SUM(Sales[UnitPrice]), and then multiply them. This is wrong! You'd be multiplying the total of all quantities by the total of all prices, resulting in a nonsensically large number.

To get the right answer, you need to first multiply the quantity by the price for each individual row, and then sum up those results. This is exactly what the SUMX function is for.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is SUMX and How Does it Work?

SUMX is an "iterator" function. This means it iterates, or goes row-by-row, through a specified table, performs a calculation for each row, and finally, adds up the results of all those individual calculations.

The syntax is a bit different from SUM:

SUMX(<table>, <expression>)

• <table>: The table you want to iterate over.
• <expression>: The calculation you want to perform for each row in that table.

Example: Calculating Total Revenue (Price x Quantity)

Let's use our example of a "Sales" table with OrderQuantity and UnitPrice columns. To create a "Total Revenue" measure, follow the same steps to create a new measure and enter this formula:

Total Revenue = SUMX(Sales, Sales[OrderQuantity] * Sales[UnitPrice])

Here’s how Power BI processes this:

1. The SUMX function starts at the first row of the "Sales" table.
2. It evaluates the expression: a quantity column multiplied by a price column for that specific row (e.g., 2 * $19.99 = $39.98).
3. It mentally stores that result ($39.98).
4. It then moves to the second row and repeats the process (e.g., 5 * $9.99 = $49.95).
5. It continues this for every single row in the "Sales" table.
6. Once it has gone through all the rows, it sums up all the stored row-level results to get the accurate total revenue.

SUMX is incredibly powerful and is a gateway to more advanced analytics, allowing you to sum data based on complex conditions and expressions that aren't readily available as single columns in your raw data.

Common Sticking Points and Best Practices

As you work with sums and measures, keep these points in mind to avoid common frustrations.

Choosing the Wrong Column to Sum

Make sure the column you are summing contains only numeric data formatted as a number (decimal, whole, currency, etc.). Trying to sum a column of text or an ID column that happens to contain numbers (like Order IDs) won't produce a meaningful business result.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Forgetting About "Filter Context"

A measure isn’t a fixed number, it's a dynamic calculation. If you put your "Total Revenue" measure in a chart that's broken down by "Country," the measure will automatically calculate the sum for each country. If you click on "Canada" in another visual, your card showing "Total Revenue" will update to show only the revenue for Canada. This powerful, automatic filtering is called "filter context," and it's a core concept of working in Power BI.

Best Practice: Always Create Explicit Measures

While the drag-and-drop feature is handy for a quick look, always get in the habit of creating explicit measures (like Total Sales = SUM(...)) for any calculation you plan to use in a final report. This makes your reports more robust, easier to maintain, and scalable.

Final Thoughts

Summing data is a fundamental skill in Power BI, forming the foundation for almost all other analysis. We've covered the quick drag-and-drop method for simple totals and moved on to the more flexible and durable DAX measures using SUM for direct column totals and SUMX for more complex row-by-row calculations. Mastering these two functions will enable you to handle the vast majority of your aggregation needs.

As you've seen, while Power BI is certainly powerful, mastering DAX takes time and practice. Even a seemingly simple task can involve learning a new syntax and a different way of thinking about your data. This is exactly why we built Graphed. We created it to eliminate this steep learning curve and help people get answers from their data in a single, unified view, using nothing more than plain English. Instead of writing DAX formulas, you can simply ask, "What was our total revenue last month?" and get an interactive dashboard in seconds, allowing you to focus on the insights, not the process.