How to SUM Multiple Ways in Power BI

We walk through how to Sum in Power BI and explain how adding numbers together is different than working in Excel. Learn how to SUM using formulas, adding columns, and how SUMX is a completely different formula than what you would expect.

Understanding Aggregation Functions in Power BI

Aggregation functions in Power BI, such as SUM, are used to summarize data and enable users to drill down into data at different levels. For example, the SUM function aggregates all the values in a column, providing a single total. That total can them be broken apart into sub-categories for financial reports, sales analyses, and performance metrics. Power BI will automatically re-calculate

Aggregation functions enable you to create visuals that can total at different levels and be expanded or drilled into for more detail. Power BI will automatically refresh and re-calculate the data as a report viewer interacts with the report.

Example of a SUM aggregation used to drill through to different reporting levels

Formulas in Power BI use a formula language called DAX, which stands for Data Analysis Expressions. It’s designed to work with large datasets and provides capabilities beyond those found in Excel.

The most common DAX formula is SUM.

SUM by Assigning Field Values in Power BI

The fastest way to SUM values in Power BI is to add a visual to your canvas, then drag fields from the data panel to the Values section. When a field with a numeric data type is assigned to Values, Power BI will automatically aggregate it with a SUM function. Click the dropdown to the right of it to to change aggregation methods.

Screenshot of assigning field values to a visual and the SUM of the Sales Quantity field being calculated

Microsoft released a more modern interface to work with Power BI called On Object Interactions designed to work better on small screens and act more like Excel. It looks different than the interface shown above, but conceptually it’s the same. You need to assign fields to rows, columns or values and set an aggregation method.

How to Sum in Power BI with DAX Measures

Power BI Measures are calculations used to aggregate data dynamically based on user interactions and filtering within reports. They include SUM, AVERAGE, MIN, MAX and can get much more complex. Measures are created using DAX Formulas.

To SUM, we will need to create a New Measure by going to the Table Tools Section of the Power BI Ribbon and select New Measure. This button is only available if you have a table selected on the data panel to the right side of your screen. The selected table will be the home of the New Measure.

The New Measure button available in the Power BI ribbon

A formula bar will appear underneath the ribbon where you can type in DAX formulas for your New Measure.

Screenshot of a SUM formula entered into the formula bar of Power BI to calculate the SUM of SalesAmount

In our example, our measure consists of two parts.

The Measure Name = the Formula, where the measure name will become a field in our data panel.

Name of New Measure = SUM(TableName[ColumnName])

Take note of the location of parenthesis and square brackets. Most formulas in DAX will require you to define the location of the column being aggregated. In the example above, we are saying we want to SUM the SalesAmount column from the Sales table.

Field names are surrounded by square brackets.

Microsoft provides additional information to help you get started with understanding and using Measures. We recommend taking a few minutes to get acquainted with the syntax of DAX as it is so widely used within Power BI.

After creating the new measure it will appear in the list of available fields that you can then drag and drop to assign to a visual on your canvas using the rows, columns, or values section of the Visualization Options panel on the right side of the screen.

Adding two SUMs Together with DAX

The SUM function in DAX is useful for adding a single column together. You can also add two columns together that each have their own SUM formula. Power BI won’t allow you to create a measure simply adding two columns together because DAX is designed to first aggregate data.

To add values from two columns together in DAX you would use a formula like the one below.

Screenshot of adding two summed numbers together in Power BI using DAX expressions.

Net Sales = SUM(Sales[SalesAmount]) + SUM(Sales[DiscountAmount])

In this case, we are adding the aggregated measures of SalesAmount and DiscountAmount. Both Columns are first aggregated and then added together.

Take note that both fields have to be aggregated first before adding them with a + sign. Because measures are aggregations, you won’t be able to add an aggregation to a column of individual values.

Scroll to Top