Power BI Sum Multiple Columns [With Examples]

In this Power BI Tutorial, we will learn about the Power BI SUM multiple columns and how to sum 2 columns in Power BI.

Additionally, we will discuss the topics below:

  • Power BI sum multiple columns
  • Power BI sum group by multiple columns
  • Power BI sum two columns from different tables
  • Power BI measure sum multiple columns
  • Power BI add two columns together
  • Power BI sum columns by row
  • Power BI dax sum multiple columns with filter
  • Power query sum multiple columns

Power BI Sum Multiple Columns

Sum multiple columns Power BI functionality allows you to add up the values in several columns of your dataset to create a new total column.

To calculate the sum of multiple columns, we can use the SUMX function in Power BI. The syntax for the Power BI Dax SUMX() function is shown below:

SUMX(<table>, <expression>)

Where:

  • SUMX = This function calculates the sum of a series of values.
  • <table> = It specifies the table from which to retrieve the values for calculation.
  • <expression> = It represents the expression to be evaluated for each row of the table, with its results summed up.

How to Sum 2 Columns in Power BI?

Let’s say you’re managing sales data in Power BI for your company. You have two columns, “Online Sales” and “Offline Sales,” each containing the total sales figures for your products. Now, you want to analyze the total sales regardless of whether they occurred online or offline.

Here, we have an Excel file that contains the Online Sales and Offline Sales columns.

sum 2 columns in power bi

Now follow the below steps:

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

sum two columns in power bi

2. Under the Modeling tab, click “New column.”

power bi add two columns together

3. In the formula bar, put the below expression. Then click Commit.

Total Sales = 'Table1'[Offline Sales] + 'Table1'[Online Sales]

Where:

  • Total Sales = This is the name of the new column.
  • Table1′[Offline Sales] = This part says, “Get the value of the Offline Sales column from Table1.”
  • ‘Table1′[Online Sales] = Similarly, this part means “get the value of Online Sales column from Table1.”
power bi sum of two columns

4. First, switch to the Table view. Then, you’ll see the total sales column that was created.

power bi sum two columns

This way, we can sum 2 columns in Power BI.

Power BI Sum Multiple Columns

Here, in this example, we calculated the sum of multiple columns using the SUMX function in Power BI.

Let’s imagine you want to track your daily sales of different product categories using Power BI.

You have a dataset with columns like “Fruits Sales,” “Vegetables Sales,” and “Dairy Sales,” each representing the sales amount for that category on a given day.

How to sum multiple columns in Power BI

Now follow the below steps:

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

How to sum two columns in Power BI

2. In the “Home” tab, click on “New measure.”

sum two columns in power bi

3. In the formula bar, type the below formula:

TotalSales = SUMX('SalesData', 'SalesData'[Fruits Sales] + 'SalesData'[Vegetables Sales] + 'SalesData'[Dairy Sales])

Where:

  • TotalSales = This is the name we’re giving to the result of our calculation, which is the total sales.
  • SalesData = This line defines a table or data source named “SalesData.”
  • ‘SalesData'[Fruits Sales] = This assigns a column within the “SalesData” table called “Fruits Sales,” which likely contains sales data for fruits.
  • ‘SalesData'[Vegetables Sales] = This assigns another column in the “SalesData” table called “Vegetables Sales,” probably containing sales data for vegetables.
  • ‘SalesData'[Dairy Sales] = Again, this assigns a column in the “SalesData” table named “Dairy Sales,” presumably containing sales data for dairy products.
sum 2 columns in power bi

4. Under the Home tab, expand Visual gallery(black box) -> Click the Table visual.

how to sum 2 columns in power bi

5. Then, using the +Add data option, add the Date, Dairy Sales, Fruits Sales, Vegetables Sales, and TotalSales into the Columns.

See also  How to Add Conditional Column in Power BI [Using Power Query]

You should now see the total sales for each day represented in the table visual.

sum of multiple columns in power bi

This way, you can create sum multiple columns in Power BI.

Power BI Sum Group by Multiple Columns

Let’s see how we can sum and group by Multiple columns in Power BI.

Let’s imagine you want to use Power BI to track your monthly earnings from each category.

Here, we have a SharePoint list (Sales Tracker) that contains below columns with various data types:

ColumnsData Types
Product CategorySingle line of text
DateDate and time
Sales AmountCurrency
power bi sum group by multiple columns

Now follow the below steps:

1. Open Power BI Desktop and load the above SharePoint List data using the Get data option. Then, you can see data in the Data pane.

power query running total by group

2. Under the Home tab, click Transform data.

Power BI sum group by Multiple column

3. In the Power Query Editor, under the Home tab, click Group BY.

sum group by Multiple columns in Power BI

4. Once the Group By window opens, choose the “Basic” option. After that, select the “Product Category” columns. Next, name the column and choose “Sum” as the operation. Finally, select the “Sales Amount” column. Then click OK.

Power BI sum group by the Multiple columns

5. Now, we can see the original table getting replaced with a Group By table with two columns: Product Category and Total Sales.

Power BI sum group by the Multiple columns example

This is how to sum and group by Multiple columns in Power BI.

Power BI Sum Two Columns From Different Tables

Let’s see how we can sum up multiple columns from different tables and display the value in Power BI.

Imagine you want to track your sales revenue and expenses separately to understand your profit.

You have two tables: one for sales and another for expenses.

Sales Table:

Power BI sum Multiple columns from different tables

Expenses Table:

Sum two columns from different tables in Power BI

Now follow the below steps:

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

Power BI sum two columns from different tables example

2. Go to “Model view.” Then, Sales Table, drag the Date column and drop it into the Expenses Table in the Date columns.

power bi sum columns from different tables

3. Under the “Home” tab click “New measure“.

dax sum two columns from different tables

4. In the formula bar, Put the below expression. Then click Commit.

Total Sales Revenue = SUMX('Sales Table', 'Sales Table'[Quantity Sold] * 'Sales Table'[Price per Unit])

Where:

  • Total Sales Revenue = This indicates that the calculation will result in the total revenue generated from sales.
  • SUMX = This SUMX function adds up the results of a calculation for each row in a table.
  • ‘Sales Table'[Quantity Sold] = This assigns the column ‘Quantity Sold’ from the table ‘Sales Table’, which likely contains the number of items sold.
  • ‘Sales Table'[Price per Unit] = This assigns the column ‘Price per Unit’ from the table ‘Sales Table’, which likely contains the price of each item sold.
power bi sum two columns from different tables

5. Under the “Home” tab, click “New measure“.

dax sum two columns from different tables

6. In the formula bar, Put the below expression. Then click Commit.

Total Expenses = SUM('Expenses Table'[Amount])

Where:

  • Total Expenses = This labels the outcome of our calculation, which is the total amount of expenses.
  • SUM() = This indicates that we’re using the SUM function, which adds together all the values within a specified column or expression.
  • ‘Expenses Table'[Amount] = Within the SUM function, we’re specifying the column ‘Amount’ from the table ‘Expenses Table’, which likely contains the individual expense amounts.
power bi combine two columns from different tables dax

7. At this point, we have total sales revenue and total expenses. Now, we can create profit using the below expression.

Profit = [Total Sales Revenue] - [Total Expenses]

Where:

  • Profit = Name of the Measure
  • [Total Sales Revenue] = Existing Measure
  • [Total Expenses] = Existing Measure
Sum Two Columns From Different Tables Power BI

Add a visual to display your profit over time or by product. This will give you insights into your business’s financial performance.

Example of Power BI sum two columns from different tables

By following these steps, you can effectively manage and analyze your sales and expense data in Power BI, helping you make informed decisions to grow your business.

See also  Power BI Measure Subtract Two Columns | Add Two Measures in Power BI

Power BI Measure Sum Multiple Columns

Suppose you want to track employee productivity by measuring the number of calls made, emails sent, and meetings attended.

You have a SharePoint List containing data for each employee’s activities.

power bi measure sum multiple columns

Now follow the below steps:

1. Open Power BI Desktop and load the above SharePoint List. Then, you can see data in the Data pane.

power bi measure add two columns

2. Under the “Home” tab, click “New measure“.

power bi measure to add two columns

3. In the formula bar, Put the below expression. Then click Commit.

Total Activities = SUMX('Activity Tracking', 'Activity Tracking'[Calls Made] + 'Activity Tracking'[Emails Sent] +'Activity Tracking'[Meetings Attended])

Where:

  • Total Activities = This names the result we’re aiming for, which is the total count of activities.
  • SUMX() = This suggests we’re using the SUMX function, which iterates over each row in a table and performs a calculation.
  • ‘Activity Tracking'[Calls Made] = This specifies the column ‘Calls Made’ from the table ‘Activity Tracking’, likely containing the count of calls made.
  • ‘Activity Tracking'[Emails Sent] = This specifies the column ‘Emails Sent’ from the table ‘Activity Tracking’, likely containing the count of emails sent.
  • ‘Activity Tracking'[Meetings Attended] = This specifies the column ‘Meetings Attended’ from the table ‘Activity Tracking’, likely containing the count of meetings attended.
power bi measure sum two columns

4. Using the Total Activities measure, we can use visuals to display the total activities for each employee.

power bi new measure sum multiple columns

By following these steps, you can effectively track and analyze employee productivity using Power BI, helping you optimize your team’s performance and achieve your business goals.

Power BI Add Two Columns Together

Let’s see how we can add the values of two columns together in Power BI.

Let’s imagine you’re managing a sports team, and you want to analyze the performance of your team based on the number of times they’ve won and lost matches.

You have a dataset (SharePoint List) called ‘Team Appearances’, which includes the counts of winning and losing times for each match your team has played.

power bi add two columns together

Now follow the below steps:

1. Open the Power BI Desktop and Load your dataset into Power BI. Make sure it includes columns for ‘Winning Time Count‘ and ‘Losing Time Count‘.

Add value of two columns together Power BI

2. Then go to “Table view.” Under the Table tools, click “New column.”

powerbi sum multiple columns

3. This will open the formula bar, where you can write below the DAX expression.

Percentage = CALCULATE(
                SUM('Team Appearances'[WinningTimeCount]) / 
                (SUM('Team Appearances'[LosingTimeCount]) + SUM('Team Appearances'[WinningTimeCount]))
            )

Where:

  • Percentage = This is the name given to the result of the calculation.
  • CALCULATE = This function is used to modify the context in which other functions or expressions are evaluated, often by applying filters or conditions.
  • SUM(‘Team Appearances'[WinningTimeCount]) = This part calculates the total sum of the ‘WinningTimeCount’ column in the ‘Team Appearances’ table, which represents the number of times a team has won.
  • SUM(‘Team Appearances'[LosingTimeCount]) = This part calculates the total sum of the ‘LosingTimeCount’ column in the ‘Team Appearances’ table, which represents the number of times a team has lost.
sum of two columns in power bi

4. Then you can see in the Table view the Percentage column created.

add two columns in power bi

This is how to add values of two columns together and display the result in the newly calculated column in Power BI.

Power BI Sum Columns by Row

Now we see Power BI sum values of multiple columns per row

Let’s say you want to track your monthly sales for different products. You have a dataset with columns for each product and rows for each month.

powerbi sum column

Now follow the below steps:

1. Open the Power query editor in Power BI Desktop and load the above table in the Power query editor.

power bi total sum of column

2. To add a column, select it by Ctrl + clicking. Next, go to the “Add column” tab, expand “Statistics,” and then click “Sum.”

Sum and total per row for calculated column in Power BI

3. Then you can see in the Power Query Editor a new column added.

power bi summarize multiple columns

By following these steps, you’ll be able to use Power BI to sum up columns by row and analyze your monthly sales data effectively.

See also  Power BI Dax Min Filter [With 13 Examples]

Power BI DAX Sum Multiple Columns with Filter

In this example, we see Power BI sum values of multiple columns per row with condition.

Imagine you want to keep track of employee expenses for different categories, such as travel, meals, and supplies. Each employee submits a monthly expense report with columns for each expense category.

You want to use Power BI to sum up each employee’s expenses, but you also want to apply a condition to include only expenses above $50.

To do this, we can use below table:

sum of column in power bi

Now follow the below steps:

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

how to sum a column in power bi

2. Then go to “Table view.” Under the Table tools, click “New column.”

sum two columns power bi

3. This will open the formula bar, where you can write below the DAX expression.

TotalExpenses = 
    CALCULATE (
        SUMX (
            'EmployeeExpense',
            IF ( 'EmployeeExpense'[Travel] > 50, 'EmployeeExpense'[Travel], 0 ) +
            IF ( 'EmployeeExpense'[Meals] > 50, 'EmployeeExpense'[Meals], 0 ) +
            IF ( 'EmployeeExpense'[Supplies] > 50, 'EmployeeExpense'[Supplies], 0 )
        )
    )

Where:

  • TotalExpenses = This is the name given to the calculation’s result.
  • CALCULATE() = This function modifies the context in which other expressions are evaluated, often by applying conditions.
  • SUMX() = This function iterates over each row of a table and calculates a sum based on an expression provided for each row.
  • EmployeeExpense = This specifies the table named ‘EmployeeExpense’ from which the data will be used for calculations.
  • IF ( ‘EmployeeExpense'[Travel] > 50, ‘EmployeeExpense'[Travel], 0 ) = This part checks if the value in the ‘Travel’ column of the ‘EmployeeExpense’ table is greater than 50. If it is, it returns the value of ‘Travel’; otherwise, it returns 0.
  • IF ( ‘EmployeeExpense'[Meals] > 50, ‘EmployeeExpense'[Meals], 0 ) = This part checks if the value in the ‘Meals’ column of the ‘EmployeeExpense’ table is greater than 50. If it is, it returns the value of ‘Meals’; otherwise, it returns 0.
  • IF ( ‘EmployeeExpense'[Supplies] > 50, ‘EmployeeExpense'[Supplies], 0 ) = This checks if the value in the ‘Supplies’ column of the ‘EmployeeExpense’ table is greater than 50. If it is, it returns the value of ‘Supplies’; otherwise, it returns 0.
power bi sumifs multiple criteria

4. Now, you’ll notice a new column added, showing the total expenses. This includes employee expenses for various categories where the expense is more than 50.

sum columns in power bi

By following these steps, you’ll be able to use Power BI to sum up values of multiple columns per row with a condition.

Power Query Sum Multiple Columns

In this example, we see how to add multiple columns in Power BI Power Query Editor.

We have a dataset containing columns for Region, Electronics, Clothing, and Home Goods.

Power Query list sum multiple columns

Now follow the below steps:

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

how to sum multiple columns in power query

2. Then, Under the Home tab, click Transform data.

power query sum two columns

3. In the Power Query Editor, under the Add Column tab, click Custom Column.

how to add two columns in power query

4. In the formula editor, write a formula to sum the sales across multiple columns. In my case, create a new column called “Total Sales” with the formula below:

= [Electronics] + [Clothing] + [Home Goods]

Where Electronics, Clothing, and Home Goods are the names of the Columns. Then click OK.

power query sum column

5. Now, you can see in the Power Query Editor Total Sales column created.

Power Query Sum Column based on another column

Some more Power BI articles you may also like:

Now, you’ll be able to use Power Query Editor to sum up multiple columns and analyze your sales data across different regions more efficiently. This article explained everything about how to sum multiple columns in Power BI, working with Power BI sum group by multiple columns, and many more like:

  • Sum two columns from different tables in Power BI
  • Power BI measure sum multiple columns
  • How to add two columns together in Power BI
  • Working with Power BI sum columns by row
  • Power BI dax sum multiple columns with filter
  • How to work with Power query sum multiple columns
>