Power BI SUM Measure | SUMX() in Power BI

This Power BI tutorial will help you work with Power BI SUM measure.

We recently worked on a Power BI Report where the sum function helped calculate all the sales values in a data set.

In this tutorial, we will see the sum measure in Power BI and a real-time example of how to use the SUM measure in Power BI. Also, we will see how to work with Power BI sum multiple measures, What is SUMX() in Power BI with various examples.

What is SUM Measure in Power BI

The SUM function adds all the numbers in a column containing the currency and numerical value in Power BI. The SUM() function returns a decimal number.

Power BI SUM function syntax:

SUM(<column>)

Where:

Column = The column name that contains the numerical values to be added.

How to use Sum Measures in Power BI

This example shows how to create an SUM measure in Power BI.

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

Let’s create a measure in Power BI to assess the functionality of the SUM function. So, we will evaluate the total sales using SUM as a Measure in Power BI. To do this, follow the below steps.

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

2. Under the Home tab, click on the New measure.

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

Total Sales = SUM(Orders[Sales])

Where:

• Total Sales = Name of the measure
• Orders = Name of the Table
• Sales = Name of the Column

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

5. Then, using the +Add data option, add Total Sales(Measure)  into the Field in the Card visual.

6. The Card visual shows the Total Sales in the Order Table.

I hope you follow the above steps. This way, you can use the SUM function in Power BI.

Power BI SUM Multiple Measure

In Power BI, you can use the SUM function to add up the values of a particular measure in your dataset. However, if you want to sum up multiple measures (calculations) and display the total, you can create a new measure that combines the results of those individual measures.

Example:

Suppose the table has columns like Date, SalesAmount, and Expenses. Using this table, we want to calculate TotalProfit using SUM multiple measures.

Let’s say you have two measures:

1. SalesAmount: Represents the total sales amount.

TotalSalesAmount = SUM(FinancialData[SalesAmount])

Where:

• TotalSalesAmount = Name of the measure
• FinancialData = Name of the Table
• SalesAmount = Name of the Column

2. Expenses: Represents the total expenses.

TotalExpenses = SUM(FinancialData[Expenses])

Where:

• TotalExpenses = Name of the measure
• FinancialData = Name of the Table
• SalesExpenses = Name of the Column

Now, we create a new measure called TotalProfit, the sum of the SalesAmount and the negative Expenses. To do this, create a new measure by using the below expression.

TotalProfit = [TotalSalesAmount] + (-[TotalExpenses])

Where:

• TotalExpenses = Name of the measure
• TotalSalesAmount = Name of the measure
• TotalExpenses = Name of the measure

Result:

Here, you see the result of the SUM multiple measures in the Power BI table visual.

In this way, you can use Power BI SUM multiple measures.

SUMX() in Power BI

The SUMX function in Power BI is used to calculate the sum of an expression for each row in a table. This function processes each row at a time, applying the specified calculation. The return value of the SUMX function is a single value of any type.

Power BI SUMX function syntax:

SUMX(<Table>, <Expression>)

Where:

• Table = This is the name of the table on which you want to apply the SUMX function.
• Expression = Here, you need to specify the expression or equation the SUMX function will use to calculate each row of the designated table.

How to Use SUMX() Function in Power BI

This example shows how to create and use the SUMX function in Power BI.

Here, I have a SharePoint list (Financial Sample) that contains below columns with various data types:

Let’s create a measure in Power BI to assess the functionality of the SUMX function. So, we will evaluate the total sales using SUMX as a Measure in Power BI. To do this, follow the below steps.

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

2. Under the Home tab, click on the New measure.

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

Total Sales = SUMX(
		'Financial Sample',
		'Financial Sample'[Units Sold] * 'Financial Sample'[Sale Price]
	)

Where:

• Total Sales = Name of the measure
• Financial Sample = Name of the Table
• Units Sold = Name of the Column
• Sale Price = Name of the Column

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

5. Then, using the +Add data option, add the Product, Units Sold, Sale Price, and Total Sales(Measure) into the Columns in the Table visual.

This is how to use SUMX() in Power BI.

Power BI SUMX with IF statement

This example shows how to use a Power BI SUMX with an if statement.

The Power BI SUMX function iterates through the rows of a table and calculates a specified expression for each row. Combined with an IF statement, you can include or exclude values conditionally.

Suppose you have a Sales table with columns like Product, Quantity, and Price, and you want to calculate the total sales only for products with a quantity greater than 5. You can use the SUMX function with an IF statement. To do this, follow the below steps.

1. Our table looks like the below screenshot.

2. Under the Home tab, click on the New measure.

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

TotalSalesQuantityGreaterThan5 = SUMX(
		'SalesTable',
		IF(
			'SalesTable'[Quantity] > 5,
			'SalesTable'[Quantity] * 'SalesTable'[Price],
			0
		)
	)

Where:

• TotalSalesQuantityGreaterThan5 = Name of the measure
• SalesTable = Name of the Table
• Quantity = Name of the Column
• Price = Name of the Column

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

5. Then, using the +Add data option, add the Product, Quantity, Price, and TotalSalesQuantityGreaterThan5(Measure) into the Columns in the Table visual.

In this way, we can use a Power BI SUMX with an if statement.

Conclusion

The SUM() function in Power BI is used to sum up a column of values in a table.

Now, you know about the Power BI SUM() measure. This tutorial taught us what the Power BI SUM() measures and how to use the SUM() in the Power BI measure. Additionally, we explained Power BI SUM multiple measures.

You may also like:

• How to Sort Month Names in Power BI Slicer?
• How to Create a Date Hierarchy in Power BI?
• How to Sort Slicer in Power BI?

Preeti Sahu

Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.