How to Sum a Measure in Power BI

Trying to sum a column in Power BI is straightforward. So when you need to sum up the result of a measure you’ve already created, it seems like wrapping it in a SUM() function should work. Unfortunately, that path leads to a quick DAX error, leaving many Power BI users confused.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Unlike regular columns, measures are dynamic calculations that don't exist as a physical set of values you can simply add up. This article will show you the correct way to sum a measure in Power BI using powerful DAX iterator functions like SUMX.

Why Can’t You Just SUM a Measure?

The core of the issue lies in understanding the difference between a calculated column and a measure in Power BI. It's a fundamental concept that, once grasped, makes DAX much less intimidating.

Calculated Columns vs. Measures

A Calculated Column is an extension of your data table. You create it using a DAX formula, and the result is calculated for each row and then stored in your data model. Think of it like a new fixed column in your spreadsheet. Because it stores a value for every single row, it consumes memory and increases the size of your Power BI file. Since it’s a physical column in your model, you can easily use it in aggregations like SUM(), AVERAGE(), etc.

A Measure, on the other hand, is not pre-calculated and stored. It’s a dynamic, on-the-fly calculation performed at the time you use it in a report visual. Its value depends entirely on the "context" provided by your report — things like filters, slicers, or the rows and columns of your table visual. Because measures are just a formula (not a column of values), the simple SUM() function has nothing to "grab onto." It doesn't know which values to add up.

For example, if you place a [Total Revenue] measure into a card visual, it calculates the total revenue for all a company's data. If you put that same measure in a table next to a list of product categories, it calculates the total revenue for each specific category. The measure adapts, the context dictates the result. This is why you can't simply SUM([Total Revenue]) — Power BI will ask, "sum of the total revenue of what?"

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Enter Iterator Functions: The 'X' Factor

When you need to perform an aggregation based on a measure, you need to use a special class of DAX functions known as iterator functions. These are the functions that usually end with an "X", like:

• SUMX()
• AVERAGEX()
• MINX()
• MAXX()
• COUNTX()

Unlike their simpler counterparts, iterator functions don't just look at a column. Instead, they require two key arguments:

1. A Table: A table that the function will "iterate" or loop through, one row at a time. This can be a physical table in your model or a virtual table you create on the fly.
2. An Expression: A calculation that SUMX will perform for each and every row of that table.

Once it has cycled through every row in the specified table and performed the calculation for each, it then aggregates the final results (in the case of SUMX, it adds them all together).

This row-by-row process is precisely what we need. It allows us to give Power BI the context it was missing. We can tell it, "for each product (row context), calculate my measure, and then sum up all of those individual results."

Step-by-Step: How to Sum a Measure Using SUMX

Let's walk through a practical business scenario with a company that has detailed data about its sales over time to bring this to life.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

The Goal

We’ve been asked to calculate the total profit generated only by our "top tier" products, defined as any product with over $1,000 in lifetime profit. Simply filtering for these products isn’t a dynamic solution, since the list can change over time.

Here’s our data model:

• A Sales table with records for each transaction (ProductID, Units Sold, Unit Price, Unit Cost).
• A Products table with details for each product (ProductID, Product Name, Category). A relationship has been created between Sales[ProductID] and Products[ProductID].

Step 1: Create a Basic Profit Measure

First, we need a base measure to calculate the profit of a given sale. In business, revenue and sales are usually tracked to assess financial health and progress towards goals, and this would work as a great example.

Go to your report, right-click your Sales table, and select New Measure:

[Total Profit] = SUMX( Sales, Sales[Units Sold] * (Sales[Unit Price] - Sales[Unit Cost]) )

This isn't just about simple arithmetic — by utilizing SUMX over the Sales table, the formula accurately calculates profit for each transaction line (Units Sold × Profit Per Unit) before tallying it all up, something not achievable by first trying to SUM each variable separately, which prevents misrepresentation in cases where totals might get cross-multiplied incorrectly.

Step 2: Create the Summarizing Measure with SUMX

Now for the main event. We need a new measure that sums our [Total Profit] measure, but only for products whose total profit exceeds $1,000. This is a classic "sum of a measure" problem. Instead of summing up a direct sales count from a column, we must aggregate based on profitability levels — exactly where outdated practices hit a roadblock. Modern practices make it possible to perform these types of tasks at scale.

This is where SUMX, teamed with FILTER, shows its real potential.

Right-click your Sales table and select New Measure:

Total Profit from Top Tier Products = SUMX( FILTER( VALUES(Products[Product Name]), [Total Profit] > 1000 ), [Total Profit] )

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Breaking Down the Formula

At first sight, that might look overwhelming, but it's simpler than you think once you break down what it's telling Power BI to do.

• SUMX(..., [Total Profit]): The outer layer is our SUMX function. It tells Power BI that when we are done iterating, we should sum up the result of the [Total Profit] measure for each step in our loop.
• FILTER(...): The first argument of SUMX is a table. We don't want to iterate over all products, just the ones meeting our profit criteria. The FILTER function lets us create a temporary, virtual table containing only the products we care about.
• VALUES(Products[Product Name]): To use FILTER, we need to supply an initial table to check against. VALUES(Products[Product Name]) provides this, producing a virtual table, i.e., a unique list made up of all the names from the 'Product Name' column in our Products table — each listed exactly once. FILTER then loops over each row of this virtual table to evaluate a condition.
• [Total Profit] > 1000: This is the logic of the condition. For each product in the list, it calculates its total profit and checks if it exceeds 1000. Only those that meet this criterion remain in the filtered table.

By the time FILTER(...) finishes, SUMX works with a table containing only highly profitable products, and sums their [Total Profit] values — giving us the final, accurate result.

The Result in Action

Now you can add your [Total Profit from Top Tier Products] measure into a card visual. No matter how many filters you apply (year, region, sales rep), this calculation will always correctly identify which products meet the "> 1000 profit" criteria within that specific context and sum their profits accordingly. You've transformed a complex, multi-step query into a dynamic KPI that updates effortlessly.

Final Thoughts

Learning how to properly sum a measure is a major step forward within Power BI's Data Analysis Expressions (DAX). The key idea is to perform row-by-row calculations on a measure using iterator functions such as SUMX, AVERAGEX, to avoid incorrect results caused by traditional aggregation methods.

While mastering DAX is a valuable skill, it demands time and effort. Instead of manually writing complex formulas each time or repeatedly asking basic questions like "What are the latest profits now this week?", tools like Graphed can produce comprehensive dashboards and insights quickly, saving your time and enabling faster decision-making. This allows you to focus on strategic analysis rather than wrestling with long, complicated formulas.