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.

Sum in Power BI is Very Different from Excel

A lot of people make the transition from Microsoft Excel to Power BI. It’s a natural progression and a great solution to enable more real time reporting and eliminate the need to continuous create Excel sheets, e-mail them to people etc. etc.

However, in Excel it’s very easy to write a formula like =A2 + B2 and this is what most people are used to doing.

Power BI works very differently than Excel. It’s important to understand a few key differences.

• Power BI was built to allow people to drill down into their data
• Power BI formulas require you to summarize or aggregate everything
• Power BI works more like a pivot table or power pivot than regular Microsoft Excel
• The Data Model and Calculations for Visuals are two different steps of a process

How to Sum in Power BI using DAX Formulas

DAX stands for Data Analysis Expressions. It’s the name of the formula formatting and code that Power BI uses. It’s not very comparable to Microsoft Excel and it’s best to think of it as its own thing.

In most cases Power BI Requires you to add a full column all at once.

The example below is the Power BI Data View. You can switch to it on the left side of the screen. It shows the columns of data available in the dataset. We are going to Sum a column named SalesAmount

From the Report View Screen click on New Measure from the Table Tools section of the toolbar at the top of the screen. You could also right click on the list of column names at the right and click Create New Measure.

A text box will appear for you to type a formula in. The syntax is as follows:

Name of New Measure = SUM([ColumnName])

Rather than typing = like you would at the start of a formula in Excel, Power BI requires you to name the measure. Think of it as a way to save your formula for later use.

The following formula will calculate the total Sales Amount, and is able to be aggregated, filtered or drilled into.

How to Add Two Numbers Together Using Power BI DAX Formulas

Expanding on the example above, you can sum two numbers together by using the Sum Formula pointed at two different columns and place a plus sign in between them. This method works the same for subtraction, multiplication any other math formula etc.

The biggest difference here is that you have to sum the two columns first, then the rest works mostly like it would in Excel.

The syntax for the formula would look like this:

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

Note: It’s also possible to add two different Measures together. Sometimes it’s easier to split calculations into multiple steps. If you have already created a SUM calculation, it’s not necessary to do it again and you can type a formula that looks like this: Total = [Measure1] + [Measure2]

How to Add Columns Together in Power BI

After you have imported your data into Power BI. Navigate to the Data View. This is notated by a small icon on the left of the screen that looks like a data table or a spreadsheet.

The Data View will allow you to make modifications to your underlying data. If you’re working with an Excel Spreadsheet or CSV File, the underlying data isn’t impacted. These steps occur within Power BI and leave your starting data intact.

Step 1.) Click On Data View

Step 2.) Click On New Column

After creating a new column, you will see a highlighted formula bar at the top of Power BI. It prompts you for the Column Name =

The Syntax you want to Use is New Column Name = Column1 + Column2 using the names of each individual column.

Adding columns can be a very useful approach in Power BI. However, when you go to add these numbers to a visual for the report you will likely need to use a DAX formula to aggregate them again. The benefit of putting them into a data model is that it is easy for other users to see if they need to edit it in the future.

In general, we are fans of splitting formulas into smaller pieces and steps to make it easier to manage later on. These steps also become reusable components that will speed up future dashboard and report development.

Using Power BI SUMX Formulas

One of the confusing formulas in PowerBI is the existence of SUMX. It often gets confused with the SUM formula but is really quite different. Rather than using it to add numbers together, it sums them and multiplies them in place.

This functionality shouldn’t be considered a Sum but a two part sum and multiply much like you would use a Microsoft Excel Array formula.

Pop! Automation is Here to Help

Contact us today if you need help building out Power BI dashboards and reports. We offer hands on training classes for businesses where we build out reports together with realistic use cases. Our courses range from beginner to advanced and can jumpstart your team.

In addition to training, we offer development and consulting services where we will consult with your process owners, create an interactive dashboard and hand it off. We often combine these services with training so that as the business changes your team can self manage. If you want to be completely hands off, we can do that to and support you in any way that you need.