Power BI DAX SUM and SUMX function – How to use

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