How to Sum Two Columns in Power BI Using DAX

Bringing data together from more than one column is one of the first hurdles you’ll encounter when building reports in Microsoft Power BI. You might have sales figures from different product lines or regions in separate columns, and you need a single, unified total. We’ll walk through how to sum two columns in Power BI using DAX expressions to create both calculated columns and measures, helping you decide which method is the perfect fit for you.

Calculated Column vs. Measure: What's the Difference?

Before writing any DAX (Data Analysis Expressions), it’s vital to understand the difference between a calculated column and a measure. They may sound similar, but they operate in fundamentally different ways and are used for different purposes.

Calculated Columns

Think of a calculated column as a permanent addition to your data table. When you create one, Power BI calculates its value for every single row in your table during the data refresh process. This new column is then stored in your data model, consuming memory and disk space just like an imported column.

• Computed When? During data refresh.
• Stored Where? In your table, visible in Data View.
• How it Works: It operates on a "row context," meaning it can perform calculations using values from other columns in the same row.
• Best Used For: Creating static values for each row that you might want to use in a slicer, a filter, or as an axis or legend on a chart. For example, creating a "Full Name" column by combining "First Name" and "Last Name" columns.

Measures

A measure, on the other hand, is a calculation that happens on the fly, right when you use it in a report visual. It doesn't create a new column or store any data directly in your table. Instead, it computes an aggregated value (like a sum, average, or count) based on the current context being applied by your report - like filters, slicers, or interactions with other charts.

• Computed When? In real-time when you interact with a report visual.
• Stored Where? The DAX formula is stored, but the result isn't stored in the table.
• How it Works: It operates on a "filter context," meaning its result changes dynamically based on the filters applied in the report.
• Best Used For: Calculating dynamic aggregate values that need to change based on user interaction. Examples include calculating total sales, average order value, or year-over-year growth.

For something like summing two columns, you can actually use either method. The best choice depends on what you want to achieve: a static, row-by-row sum (calculated column) or a dynamic, aggregated total that reacts to your report (measure).

Method 1: Summing Row-by-Row with a Calculated Column

Let's start with the most straightforward approach: creating a calculated column. This is the right choice when you need to see the combined total for each individual entry in your table. Imagine you have a 'Sales' table with sales figures for two different products, 'Website Sales' and 'In-Store Sales', and you want a third column showing the total sales for each transaction.

Here’s how you can do it step-by-step:

Step 1: Navigate to the Data View

In the Power BI interface, look at the panel on the left-hand side. Click on the grid icon to switch to the Data View. This view shows you the raw data in your tables.

Step 2: Select Your Table and Add a New Column

From the 'Fields' pane on the right, select the table you want to modify (in our case, the 'Sales' table). Then, in the 'Column tools' tab located on the top ribbon, click on the New column button.

Step 3: Write Your DAX Formula

A formula bar will appear above your table. This is where you'll write your DAX expression. The syntax is simple: you reference the columns you want to add together using TableName[ColumnName].

For our example, type the following formula and press Enter:

Total Sales = Sales[Website Sales] + Sales[In-Store Sales]

In this formula:

• Total Sales is the name we’re giving our new column.
• Sales[Website Sales] refers to the 'Website Sales' column within the 'Sales' table.
• + is the simple addition operator.
• Sales[In-Store Sales] refers to the 'In-Store Sales' column within the 'Sales' table.

Step 4: Review the Result

As soon as you press Enter, Power BI calculates the sum for every row and a new column named 'Total Sales' will appear in your table. Each cell in this new column contains the sum of the web and in-store sales for that specific row.

Now you can use this 'Total Sales' column just like any other column in your dataset – for example, to see which specific transaction had the highest combined sales.

Method 2: Calculating the Overall Total with a Measure

A calculated column is great for row-level analysis, but most of the time you want to see an overall, aggregated total. For that, a measure is the perfect tool. A measure won’t add a new column to your table, instead, it creates a calculation you can drag into visuals (like cards or charts) to display a total that updates dynamically as you apply filters.

Let's create a measure to calculate the total sum of our 'Website Sales' and 'In-Store Sales'.

Step 1: Navigate to the Report View

Click on the bar chart icon in the left-hand panel to go to the Report View, where you build your visualizations.

Step 2: Create a New Measure

Find your 'Sales' table in the 'Fields' pane, right-click on it (or click the three dots), and select New measure. You can also click the 'New measure' button in the 'Home' or 'Modeling' tab of the top ribbon.

Step 3: Write the Aggregating DAX Formula

The formula for a measure is slightly different because it needs to know how to aggregate the columns before summing them. We use the SUM() function for this.

Enter the following formula in the formula bar:

Total Sales (Measure) = SUM(Sales[Website Sales]) + SUM(Sales[In-Store Sales])

This formula tells Power BI to:

• First, calculate the sum of the entire 'Website Sales' column.
• Next, calculate the sum of the entire 'In-Store Sales' column.
• Finally, add those two grand totals together.

Step 4: Use Your Measure in a Visual

After you press Enter, you won't see any immediate changes in your table. However, you will see your new measure appear in the 'Fields' pane, marked with a small calculator icon. Now, you can use it!

To see it in action:

• Select a Card visual from the 'Visualizations' pane.
• Drag your new 'Total Sales (Measure)' from the 'Fields' pane onto the 'Fields' area of the card visual.

The card will now display the grand total of all web and in-store sales combined. The real power of this measure is its dynamic nature. If you add a slicer for 'Year' to your report and select "2023," the card will automatically update to show you the total sales for just that year.

Advanced Scenarios and Best Practices

Once you're comfortable with the basics, a few more advanced DAX functions can make your calculations more robust and flexible.

Handling Blank Values

What happens if one of your sales columns has a blank entry? If a transaction was purely online, the 'In-Store Sales' column might be blank. The simple + operator in a calculated column can sometimes return blank if one of the values is blank. To avoid this, you can use the COALESCE() function, which replaces blank values with a specified value (like 0).

Your calculated column formula would look like this:

Total Sales = Sales[Website Sales] + COALESCE(Sales[In-Store Sales], 0)

This ensures that if 'In-Store Sales' is blank, it's treated as zero, and the calculation still gives a correct result for the website sales.

Using SUMX for more complex situations

Sometimes you need to perform a row-by-row calculation first and then sum up the results inside of a single measure. While SUM(Table[Column1]) + SUM(Table[Column2]) works perfectly for this example, the SUMX() function is an incredibly powerful tool for more complex scenarios.

SUMX is an "iterator" function. It goes through a table row-by-row, performs a specified calculation for each row, and then sums up the results. To accomplish the same goal as our measure above, the formula would be:

Total Sales (SUMX) = SUMX(Sales, Sales[Website Sales] + Sales[In-Store Sales])

This may seem like more work for a simple sum, but it becomes essential when you need to mix aggregations, like SUMX(Sales, Sales[Quantity] * Sales[Unit Price]). It establishes a more flexible and powerful pattern for when your DAX needs become more complex.

Final Thoughts

So, there you have it. You can sum columns in Power BI by creating a static calculated column for row-level analysis or a dynamic measure for powerful, aggregated visuals. For single-row totals, a calculated column is your answer, for displaying an interactive grand total in a report, a measure is the way to go.

As you've seen, getting started with DAX can have a bit of a learning curve, and it’s easy to get tangled up in remembering function names or picking between a measure and a calculated column. At Graphed, we’ve focused on removing that friction, so creating advanced reports is as simple as asking a question. For instance, instead of writing DAX, we allow you to simply type, "Show my total Website Sales and In-Store Sales combined as a single score card," and the visualization is built instantly. It helps you get insights from your connected data in seconds, without ever needing to worry about the underlying formulas. You can sign up for a free trial of Graphed today and start building dashboards faster.