In this Power bi tutorial, we will learn about the two aggregation functions with examples i.e SUM() and SUMX(). Apart from Power BI DAX SUM and SUMX function, we will discuss the below points:
- Power bi Dax SUM function
- Power bi Dax SUMX function
- Power bi dax sum vs sumx
- When to use SUM() and SUMX()
- Power bi SUM and SUMX examples
Power Bi SUM function
The Power bi sum function will add all the numbers in a column, and the column contains numbers to sum. It returns a decimal number.
The syntax for the Power BI SUM Function
Sum= SUM(<column>)
If we want to filter the values that we are summing then we can use the SUMX function and specify an expression to sum over.
Power bi Dax SUMX function
The Power bi SUMX function returns the sum of an expression for each row in a table, whereas the table containing the rows for which the expression will be evaluated.
The power bi SUMX function takes as its first argument a table or an expression that returns a table. And the second argument of the function is a column, that contains the number you want to sum.
The syntax for the Power bi Dax SUMX function
Sumx = SUMX(<table>,<expression>)
Read How to create a table in Power bi
Power bi dax sum vs sumx
Here we will see the difference between power bi DAX SUM vs SUMX function.
SUM() | SUMX() |
---|---|
SUM() is the aggregation function | SUMX() is the iteration function |
SUM() adds up all the values in a single column we specify after applying all filters that will impact the formula | SUMX() works through a table row by row to complete the evaluation after applying all filters. |
Power bi SUM function operates over a single column and has no awareness of individual rows in the column, i.e. no row by row evaluation | Power bi SUMX function can operate on a single column and multiple columns in the table and also it can operate row by row in those columns |
Read Power bi measure examples (20 useful examples)
When to use SUM() and SUMX()
Here we will see when to use sum() and sumx() in power bi dax.
As we discussed SUM is an aggregation function, so it summarizes the value based on filter context.
Let’s see with an example by creating a measure using SUM()
- So here we will calculate the SUM of sales price using measure.
Total sales price = SUM( Sheet[Sales price])
Now if we want to calculate the profit i.e. sales price – cost price by using a measure
As you can see in the below screenshot, the input is just one column name for the SUM function, so we cannot minus one column with another column.
There is one way to solve this problem, i.e. multiple SUM functions. Here is the formula
Profit = Sum(Sheet[Sales price])-SUM(Sheet[Cost price])
But for the long-expression, this way of writing an expression is hard to readable. If you use the SUM() to calculate each column, it will be hard to read and understand the expression. In this case, we can use the SUMX(), the SUMX is the sum of an expression.
To work with SUMX() we need to specify a table name because we may write an expression that uses a column from another table. So the expression for Profit would be
Profit = SUMX(Sheet, Sheet[Sales price]-Sheet[Cost price])
Read Power BI combine columns from two tables
Power bi sumx example
Here we will see different examples of SUM and SUMX function in power bi.
Example 1: Power bi dax sum multiple columns
Here we will see how to sum multiple columns using sumx function in the power bi desktop.
We will use the below sample table to sum the multiple columns using sumx function in the Power BI measure.
- Load the data using get data.
- Click on the new measure from the ribbon.
- Then write the below measure
Measure = SUMX(Sheet1, Sheet1[Value 1]+Sheet1[Value 2]+Sheet1[Value 3])
Now in the below screenshot, you can see the power bi dax sum multiple columns.
Read Power bi measure count with filter
Example 2: Power bi sumx group by
Here we will see how to calculate the running sum of average sales per day per country using the SUMX function.
We will use the below sample table having three columns i.e country, date, and sale.
- Load the data using get data.
- Click on the new measure from the ribbon.
- Then write the below measure
Sales_Avg_RunSum_per_day =
VAR currDate =
MAX ( 'Calendar'[Date] )
RETURN
SUMX (
SUMMARIZE (
CALCULATETABLE ( 'Data', 'Calendar'[Date] <= currDate ),
"AVG Value", AVERAGE ( 'Data'[Sale] )
),
[AVG Value]
)
In the below screenshot, you can see the sum of sales, group by measure per country
Read Power BI MAX and MIN function with Examples
Example 3: Calculate the total sales using SUMX()
Here we will see how to calculate the total sales using SUMX() in power bi.
We will use the below sample table having three columns are Region, price, and units columns.
Now we will calculate the total sales =Price *Units.
- Load the data using get data.
- To calculate the total sales, we eill create a new column, so click on the Modelling tab -> New column.
- Then write the below Dax formula.
Total sales = SUMX(Sheet2, Sheet2[Units]*Sheet2[Price])
Now in the below screenshot, you can see total sales using SUMX()
If we want to calculate the total sales using the measure. Click on the new measure.
Then write the below measure:
Sales value = SUMX(Sheet2, Sheet2[Price]*Sheet2[Units])
Read Power BI Measure multiply
Example 4: Calculate the total quantity using SUMX()
Here we will see using Power BI SUMX() function, how to calculate the total quantity in power bi.
We have sample data, having three columns, id, quantity, and days.
We will calculate the total quantity = Quantity * Days using the measure.
- Load the data using get data
- Click on the new measure from the ribbon.
- Then write the below measure.
Total Quantity = SUMX(Data1, Data1[Days]*Data1[Quantity ])
In the below screenshot you can see the total quantity using SUMX()
Example 5: Calculate the total sales price using SUM and SUMX function
Here we will see how to calculate the total sales price for product 1 using SUM and SUMX() in measure.
We will use the below sample data, having fur columns i.e. product, Quantity, Sales price, and cost price.
First, we will calculate the total sales for product 1 using SUM(), so calculating only for product 1 we will use the filter.
But Sum() only takes one column as an input. To solve this problem we will use the CALCULATE(), which will accept the Sum() as well as a filter ().
- Load the data using get data.
- Click on the new measure form the ribbon.
- Then write the below measure
Total SP = CALCULATE(SUM(Sheet[Sales price]),FILTER(Sheet, Sheet[Product]="Product 1"))
In the below screenshot you can see the total sale price for product 1 using SUM().
Now we will calculate the same thing using the SUMX function, but here we don’t need the CALCULATE(), because SUMX() accepts the table and expression as input.
- Click on the new Measure form the ribbon
- Then write the below measure
Total sales price = SUMX( FILTER(Sheet, Sheet[Product]="Product 1"), [Sales price])
In the below screenshot, you can see the Total sales price using SUMX().
You may also like the following Power BI tutorials:
- Power bi measure divide
- Microsoft Power BI Combo Chart
- Power Bi key influencers
- How to change data source in Power BI
- Power BI Export Data
In this power bi tutorial, we will learn about Power bi SUM() and SUMX(). And also we will discuss the below points:
- Power bi Dax SUM function
- Power bi Dax SUMX function
- Power bi dax sum vs sumx
- When to use SUM() and SUMX()
- Power bi SUM and SUMX examples
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com
How can we calculate running total per account for last X number of days. Considering we have three main columns as Account Key, Amount, Date. For each current day we want to know sum of amount for past x days for each account.
Account no. Amount Date Answer Expected for x = 2
a 1000 11/5/22 2100
a 1100 11/5/22 2100
a 1700 12/5/22 3800
a 800 13/5/22 4600
b 1900 10/5/22 1900
Superb Content.thank you