In this Power BI Tutorial, we will learn about the Power BI SUM multiple columns with a few examples. We will also see how to filter, sum, and group by the various columns in Power BI.
- Power BI sum Multiple columns
- How to sum multiple columns in Power Bi
- Power BI sum Multiple columns with filter
- Power BI sum Multiple columns in a Matrix
- Power BI sum group by Multiple columns
- Power BI sum group by Multiple columns with filter
- Power BI sum Multiple columns from different tables
- Power BI sum multiple columns in a table
- Power Bi sum multiple columns yes or no
- Power BI sum multiple columns percentage
- Power BI cumulative sum by multiple columns
- Power Query list sum multiple columns
- Power BI sum multiple columns zero
- Power BI running total multiple columns
- Power BI sum multiple columns unique
Power BI sum Multiple columns
To Sum Multiple columns in Power BI, we use the SUM() or SUMX() function in Power BI,
- The Power BI Sum() function will add all the numbers in a column-based manner, it calculates or Sums the values column-wise and returns the value.
- The syntax for the Power BI SUM() Function is mentioned below:
Sum= SUM(<column>)
- The Power BI SUMX() function returns the sum value for each row in a table, The power BI SUMX() function takes the first argument of a table and the second argument of the function is an expression, that contains the number you want to sum.
- The syntax for the Power BI Dax SUMX() function is shown below:
Sumx = SUMX(<table>,<expression>)
Now in the below heading, we will see how to sum multiple columns in Power BI. Here In this example, I have used this excel file data.
How to sum multiple columns in Power BI
Let us see how to sum multiple columns using the SUMX function in Power BI,
- In this example, I have used the below sample table (Product) to calculate or sum the multiple columns (SP and CP) using the sum function in the Power BI measure.
- Make sure you have Loaded the data using the get data option on the Power BI desktop. Now, click on the New measure from the ribbon and apply the below-mentioned formula:
SUM of SP and CP = SUMX(Products, Products[SP]+Products[CP])
Where,
- SUM of SP and CP =Measure Name
- Products = Table Name
- SP,CP = Column names (Number data type)
- Once the Measure is created drag and drop the created measure in the value field.
- In the below screenshot, you can see that it displays the sum value using the SUMX function of multiple columns in Power BI.
This is how to sum multiple columns using the SUMX function in Power BI.
Also, read: Power BI divides two columns [With Examples]
Power BI sum Multiple columns with filter
Let us see how to sum multiple columns with a filter using the SUMX function in Power BI,
- In this example, I have used the below sample table (Product) to calculate or sum the multiple columns using the sum function in the Power BI measure.
- First, we will calculate the total of SP and CP, after that we will apply a filter for Product A.
- Initially Load the data using the Get data option and click on the new measure from the ribbon. Then apply the below-mentioned measures.
SUM of SP and CP = SUMX(Products, Products[SP]+Products[CP])
Filter = SUMX( FILTER(Products, Products[Product]="Product A"), [SUM of SP and CP])
Where,
- SUM of SP and CP =Measure Name
- Products = Table Name
- SP, CP = Column names
- Once the Measure is created drag and drop the created measure in the value field.
- In the below screenshot you can see the Sum of the value is calculated and based on the filter it displays the Product A value in the card visual.
This is how to sum multiple columns with a filter using the SUMX function in Power BI.
Check out: Stacked Bar Chart in Power BI [With 27 Real Examples]
Power BI sum Multiple columns in a Matrix
Let us see how we can sum multiple columns using the SUM function in Power BI and display the result in the matrix visual,
- In this example, I have used the below sample table to calculate or sum the multiple columns using the sum function in the Power BI measure.
- Here, we will calculate the total value of SP and CP using the SUM function measure in Power BI.
- Initially Load the data using the get data option and click on the new measure from the ribbon. Then apply the below-mentioned measures using the SUM function.
- When we use the SUM function, by default it takes the column count value, To solve this problem we can use the CALCULATE() function as shown below:
SUM of SP and CP = CALCULATE(SUM(Products[SP])+SUM(Products[CP]))
Where,
- SUM of SP and CP =Measure Name
- Products = Table Name
- SP, CP = Column names
- Once the Measure is created drag and drop the created measure in the value field.
- Now select the matrix visual under the visualizations, and drag and drop the field that you want to display in it.
- In the below screenshot, you can see the Sum value is displayed in the Matrix visual using the SUM function.
This is how to sum multiple columns using the SUM function in Power BI and display the result in the matrix visual.
Read: Power BI Bookmarks [With 21 Examples]
Power BI sum group by Multiple columns
Let us see how we can sum and group by Multiple columns in Power BI.
For example, here we will use the below table to sum and group the Sales based on the Product and Country.
- In the Power BI desktop click on the Transform Data from the ribbon, to open the power query editor.
- In the power query editor, Click on Transform tab -> Group by. click on Group By icon from the ribbon.
- Then the Group By window will appear, Click on the Advanced options, -> choose the Country column -> click on Add grouping -> the select Product column.
- Next, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.
- Now, we can see the original table getting replaced with Group By table, having three columns i.e Country, Product, and Total Sales.
This is how to sum and group by Multiple columns in Power BI.
Also read: Clustered Column Chart in Power BI [With 45 Real Examples]
Power BI sum group by Multiple columns with filter
Let us see how we can sum and group by the Multiple columns with a filter in Power BI,
For example, here we will use the below table to sum and group the Sales based on the Product and Country.
- In the Power BI desktop click on the New Table under the table tools from the ribbon as highlighted below:
- Then write the below-mentioned DAX formula in Power BI.
Total Sales = SUMMARIZE(Sales,Sales[Product],"Country", CONCATENATEX(Sales,Sales[Country],","), "Sales",SUM(Sales[Sales]))
Where,
- Total Sales = New Table Name
- Sales = Existing Table Name
- Product, Country, Sales = Column names
- Now, below the screenshot, you can see that the New Total sales table displays the data with Group By table, having three columns i.e Country, Product, and Sales.
- In the Total Sales table, Click on the new measure from the ribbon. Then apply the below-mentioned measures to apply a filter based on the product.
GroupbyFilter = SUMX( FILTER('Total Sales', 'Total Sales'[Product]="VTT"),'Total Sales'[Sales])
Where,
- Total Sales = Newly created Table Name
- Sales = Existing Table Name
- Product, Country, Sales = Column names
- Once the Measure is created drag and drop the created measure in the value field.
- In the below screenshot, you can see the Sales value is displayed in the card visual.
- Based on the filter applied it displays the Product VTT sales value in the card visual.
This is how to sum and group by the multiple columns with a filter using the SUMX and Filter function in Power BI.
Read: Power bi change color based on value [With 13 real examples]
Power BI sum Multiple columns from different tables
Let us see how we can sum up multiple columns from different tables and display the value in Power BI.
Here in this example, I have selected two different tables of data as shown below and we have two tables in which each table has one column common i.e Product column as common.
Table â€“ 1: (Sales Table)
Table â€“ 2: (Profit Table)
First, we will sum the Sales Colum in Sales Table with the Profit column in Profit Table according to Product.
- Initially load the data using the get data option to the Power BI desktop, Now click on the new measure from the ribbon and then write the below measure:
SUM of Sales and Profit = SUMX(Sales,Sales[Sales Column]) + SUMX(Profit,Profit[Profit Column])
Where,
- SUM of Sales and Profit = Measure Name
- Sales, Profit = Existing Table Names
- Sales Column, Profit Column = Column names
- Check the measure, and select the table visual from the visualization pane.
- In the value field, drag and drop the Product, Country, Sales column, Profit column, and Sum of Sales and Profit measure from the field pane.
- In the below screenshot, you can see that Power BI sum up the multiple columns from the two different columns in Power BI.
This is how, to sum up, multiple columns from different tables and display the value in Power BI.
Power BI sum multiple columns in a table
Let us see how we can sum multiple columns in a table Power BI,
In this example, I have used the below sample table to calculate or sum the multiple columns using the sum function in the Power BI measure.
- Make sure you have Loaded the data using the get data option on the Power BI desktop. Now, click on the new measure from the ribbon and apply the below-mentioned formula
SUM of SP and CP = CALCULATE(SUM(Products[SP])+SUM(Products[CP]))
Where,
- SUM of SP and CP =Measure Name
- Products = Table Name
- SP, CP = Column names
- Once the Measure is created drag and drop the created measure in the value field.
- Now, select the table visual under the visualizations, and under the column, drag and drop the fields that you want to display in the Table visual.
- It displays the sum value using the SUM function of multiple columns in Table Power BI.
In the below screenshot, you can see that the sum of multiple columns in a table is calculated and displayed in the Table visual.
This is how to sum multiple columns in a table using the SUM function in Power BI.
Also, check out this post: Power BI calculated column [With 71 Useful Examples]
Power Bi sum multiple columns yes or no
Let us see how we can create a SUM measure with conditions in Power BI.
For example, here we have created a sample data having Products, Country, sales, and Profit as shown below:
- Now we will create a measure and apply the formula, if the Sales count is greater than 1200 then it should display the value Yes else it should display the value No.
Sum_if = IF(SUM(Profit[Sales]) >= 1200, "Yes", "No")
Where,
- Sum_if = Measure Name.
- Profit = Table name
- Sales = Column name
- Once the Measure is created drag and drop the created measure in the value field.
- Select the Table or Matrix visual under the visualizations, and drag and drop the column fields that you want to display in the visual.
- In the below screenshot, you can see that it displays the Yes / No value using the SUM function of multiple columns in Power BI. If the Sales value is greater than 1200 then it displays a yes value else it displays no value.
This is how to create a SUM measure with conditions in Power BI.
Check out: Power BI if date + 27 Examples
Power BI sum multiple columns percentage
Let us see how we can sum multiple columns Percentage in Power BI,
In this example, I have used the below sample table to calculate or sum the multiple columns using the sum function in the Power BI measure.
- Make sure you have Loaded the data using the get data option on the Power BI desktop. Now, click on the new measure from the ribbon and apply the below-mentioned formula
Measure3 = SUM(Products[SP+CP])
SP+CP/CP = [Measure3]/Products[Sum of CP Column]
Where,
- Measure3 and SP+CP/CP = Measures Name
- Products = Table Name
- SP and CP = Column names
- Once the Measure is created drag and drop the created measure in the value field.
- Select the Table or Matrix visual under the visualizations, and drag and drop the column fields that you want to display in the visual.
- Make sure, you have selected the measure and changed the data type format from Wholenumber to Percentage.
- In the below screenshot, you can see that it displays the percentage value using the SUM function of multiple columns in Power BI.
This is how to sum multiple columns and display the Percentage value in Power BI.
Also read: Power BI IF + 31 Examples
Power BI cumulative sum by multiple columns
Let us see how we can sum the cumulative sum by multiple columns in Power BI,
- In this example, I have used the below sample table to calculate the cumulative sum of the multiple columns using the sum function in the Power BI measure.
- Here, we will calculate the total value of SP and CP using the SUM function measure in Power BI.
- Initially Load the data using the get data option and click on the new measure from the ribbon. Then apply the below-mentioned measures using the SUM function.
- When we use the SUM function, by default it takes the column count value, To solve this problem we can use the CALCULATE() function as shown below:
Cumulative Sum = CALCULATE(SUM(Products[SP])+SUM(Products[CP]))
- Cumulative Sum =Measure Name
- Products = Table Name
- SP, CP = Column names
- Once the Measure is created drag and drop the created measure in the value field.
- Now select the matrix or table visual under the visualizations, and drag and drop the field that you want to display in it.
- In the below screenshot, you can see the Sum value is displayed in the visual using the Cumulative SUM function.
This is how to sum the cumulative sum by multiple columns in Power BI.
Read: Power Query Examples
Power Query list sum multiple columns
Let us see how we can sum the two or more column list using Power Query in Power BI,
In this example, I have used the below sample table to sum the two columns using the Power Query editor in Power BI.
- Here, we will calculate the sum of sales and profit value using the power query editor in Power BI.
- Initially Load the data using the get data option and click on the Home and Select the Transform data option from the ribbon as shown below:
- Now, In the Power Query editor, Select the Table and click on the add column option.
- After that, select the column that you want to add with other columns Expand the standard option and select the operation that you want to perform.
- Here I am going to select Add operation as mentioned below:
- Under the values, select the columns that you want to add, and click on the Ok button.
- Now, you can see the Addition column displays the result of sales + profit columns using Power Query in Power BI.
- Click on the Close and Apply option from the ribbon, so that the changes will be saved.
This is how, to sum up, the two or more column lists using Power Query in Power BI.
Check: Power BI split column [With 13 real examples]
Power BI sum multiple columns zero
Let us see how we can sum two columns and display the result in integers in Power BI,
- yes, it is possible to display the calculated result in the integer format, where the result value is greater than 1 then it displays the value 1 else it displays the value 0.
- In this example, I have used the below sample table to calculate or sum the multiple columns using the sum function and display the result in the integer format using the Power BI measure.
- Make sure you have Loaded the data using the get data option on the Power BI desktop. Now, click on the new measure from the ribbon and apply the below-mentioned formula
Measure = IF(SUM(Profit[Sales]) + SUM(Profit[Profit]) > 1, 1, 0)
Where,
- Measure = Measure Name
- Profit= Table Name
- Sales and Profit = Column names
- Now, below the screenshot, you can see that the New Measure displays the data with Group By table, having four columns i.e Country, Product, Sales, and Profit.
- After selecting the created measure that you have displayed in the table it will display the integer value if the sum value is greater than 1 then it displays 1 else 0.
- In the below screenshot, you can see the Measure value is displayed based on the sum value of profit and sale in the table visual.
This is how to sum two columns and display the result in an integer.
Read: How to create a report in Power BI Desktop? [Excel & SharePoint]
Power BI running total multiple columns
Let us see how we can calculate the running total of the Multiple columns using the Power BI measure,
- In this example, I have used the below sample table to calculate the running total of multiple columns in Power BI.
- Make sure you have Loaded the data using the get data option on the Power BI desktop. Now, click on the new measure from the ribbon and apply the below-mentioned formula
Running total = CALCULATE( SUM(Profit[Sales]),
Profit[Sales] >= (Profit[Profit])
)
Where,
- Running total = Measure Name
- Profit = Table Name
- Sales and Profit = Column names
- Now, below the screenshot, you can see that the New measure displays the data with Group By table, having four columns i.e Country, Product, Sales, Profit, and Running total.
- After selecting the created measure that you have displayed in the table it will display the result based on the condition applied in the Measure.
- In the below screenshot, you can see the Measure value is displayed as the running total value when the sales value is greater than the profit value.
This is how to calculate the running total of the Multiple columns using the Power BI measure.
Also, check: Power BI Date Slicer [With 41 real examples]
Power BI sum multiple columns unique
Let us see how we can display the sum of multiple column unique values in Power BI,
- In this example, I have used the below sample table to calculate the uniqueness of multiple columns in Power BI.
- Make sure you have Loaded the data using the get data option on the Power BI desktop. Now, click on the new measure from the ribbon and apply the below-mentioned formula
Unique value count =
VAR totalcount =
FILTER (
SUMMARIZE (
UNION (
VALUES ( Profit[Product] ),
VALUES ( Profit[Country] )
),
Profit[Product]
),
Profit[Product]<> BLANK ()
)
RETURN
COUNTROWS ( Unique value count)
Where,
- Unique value count = Measure Name
- var totalcount = variable name
- Profit = Table Name
- Country and Product = Column names
- Now, below the screenshot, you can see that the New measure displays the data with Group By table, having four columns i.e Country, Product.
- Drag and drop the created measure that you have created in the card visual it will display the result based on the condition applied in the Measure.
- In the below screenshot, you can see the Measure value is displayed the unique value of product and country column as shown below:
This is how to display the sum of multiple column unique values in Power BI.
Also, you may like some more below Power BI Tutorials:
- Power BI Add Calculated Column [With Various Examples]
- Power BI sync slicers
- Power BI filter between two dates
- Line Chart in Power BI [Complete Tutorial with 57 Examples]
- Power BI Group By [With 51 real examples]
- Power BI date hierarchy [With 21 real examples]
- How to remove rows in power query editor
In this Power BI Tutorial, we have learned how to sum two columns using the SUMX function with some examples and also covered the below-mentioned topics:
- Power BI sum Multiple columns
- How to sum multiple columns in Power Bi
- Power BI sum Multiple columns with filter
- Power BI sum Multiple columns in a Matrix
- Power BI sum group by Multiple columns
- Power BI sum group by Multiple columns with filter
- Power BI sum Multiple columns from different tables
- Power BI sum multiple columns in a table
- Power Bi sum multiple columns yes or no
- Power BI sum multiple columns percentage
- Power BI cumulative sum by multiple columns
- Power Query list sum multiple columns
- Power BI sum multiple columns zero
- Power BI running total multiple columns
- Power BI sum distinct multiple columns
- Power Bi sum multiple columns unique
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site EnjoySharePoint.com