Power BI Measure SUM – Complete tutorial

In this Power BI Tutorial, we will learn about Power BI Measure SUM. Also, we will discuss:

• Power BI Measure sum
• Power BI Measure sum multiple columns
• Power BI Measure sum multiple measure
• Power BI Measure sum date
• Power BI Measure sum if column contains
• Power BI Measure sum IF
• Power BI Measure sum with filter
• Power BI Measure sum with condition
• Power BI Measure sum by group

If you are new to Power BI measure, check out an article on How to create a measure in Power BI.

Power BI Measure sum

In Power BI we can use SUM() function in both measure and calculated columns. It adds all the numbers in a column. The column must contain the numerical or number value. For creating a Measure SUM, the syntax is:

Measure = SUM(column)

For example, we have created a simple table having products and No.of users like below.

Let’s create a measure to see how this function works. So we will evaluate the total number of users by using SUM in a Measure.

Total no. of user = SUM('Table'[No.of user])

This is how to do Power BI Measure SUM.

Read What is the difference between calculated column and measure in Power BI

Power BI Measure sum multiple columns

Now we will see how a SUM works with multiple columns using Measure in Power BI Desktop.

For this, we are going to use below sample data which having the Products’ name, their profit, and sales.

Here we will create a measure that calculates the sum of two columns (i.e. Profit and Sales). For this insert the expression on the formula bar.

Sum of Profit_Sales = SUM(financials[Profit]) + SUM(financials[ Sales])

This is how we can evaluate the SUM of multiple columns using Power BI Measure.

Read Power bi slicer contains with examples

Power BI Measure sum multiple measure

Here we will calculate the SUM of multiple measures using a Power BI Measure. For example, we are going to use the previous product table with its profit, sales data.

First, we will evaluate the SUM for Profit using Measure:

Total Profit = SUM(financials[Profit])

The SUM for Sales using Measure:

Total sales = SUM(financials[ Sales])

Now we will create another measure that calculates the Net sales using both measures:

Net Sales = CALCULATE([Total Profit] + [Total sale])

This is how to calculate Power BI Measure sum multiple measures.

Read How to get selected value from Slicer in Power BI

Power BI Measure sum date

Now we will see how to evaluate the sum between dates using Power BI Measure.

For example, here we have created a table having products, it’s order date, and delivery date like below:

Now we will create a measure that returns the no. of days and the total no. of days between dates:

No Of Days = SUMX('Table', DATEDIFF('Table'[order date], 'Table'[Delivery date], DAY))

This is how we can calculate the days in between two dates and it’s the sum by using Measure.

Read Power BI Slicer Search Box

Power BI Measure sum with condition

In Power BI, we can create a SUM measure with conditions. For example, here we have created a sample data having Products and the no. of users of them like this:

Now we will create a measure that will calculate the sum of total users of a particular product from this table.

Measure 2 = CALCULATE(SUM('Table'[No.of user]), 'Table'[Product]="HP")

The above code indicates that it will calculate the total no. of users of the HP product.

We can see it adds only that product, on which we applied condition. This is how Power BI Measure SUM with condition works.

Read Power BI DAX SUM and SUMX function

Power BI Measure sum IF

Now we will see how to use IF function in Measure to calculate the SUM. Again we are going to use this table having Product’s details and no. of users data, that we recently created.

Let’s create a measure, that calculate the sum of the total users of each product. Also, if the total user is greater than 5000 then it will return True value (ex- Business) otherwise it returns False value (ex- Global).

Measure sum_if = IF(SUM('Table'[No.of user]) > 5000, "Business", "Global")

We can see by applying this measure it defined the products and their total user whether it is Global or Business conditionally.

Read Power BI Slicer Buttons

Power BI Measure sum with filter

In Power BI, we will see how to create measures that calculate the sums of filtered items.

Here we are going to use sample data based on some products. From where we will calculate the sum of the profit of a particular filtered product.

Now we will create a measure that filters the segment (i.e Government) and returns the total sum of the sales.

Measure sum_filter = CALCULATE(SUM(financials[ Sales]), FILTER(ALL(financials[Segment]), financials[Segment]="Government"))

This is how we can apply filters and calculate the SUM of a measure.

Read Power BI Slicer Dropdown

Power BI Measure SUM if column Contains

Now we will see how a measure works with condition and Contains() by calculating it’s SUM in Power BI.

For example, we have a Order(Click here to Download sample data)table having product category, ship mode, profit like this:

Here we will create a measure that calculates only those value which contains value according to our conditions.

Measure_sum_if_contains = CALCULATE (

    SUM ( Orders[Profit] ),

    FILTER (

        'Orders',

        CONTAINS ( 'Orders', 'Orders'[Product Category], "Furniture" )

            && CONTAINS ( 'Orders', 'Orders'[Ship Mode], "Regular Air" )

    )

)

This will show the sum of those product which contains both Furniture and Regular Air.

Let’s have a another example that will show the sum of those product which contains either Furniture or Regular Air(Using OR operator).

Measure_sum_if_contains = CALCULATE (

    SUM ( Orders[Profit] ),

    FILTER (

        'Orders',

        CONTAINS ( 'Orders', 'Orders'[Product Category], "Furniture" )

            || CONTAINS ( 'Orders', 'Orders'[Ship Mode], "Regular Air" )

    )

)

This is how a Power BI Measure sum if contains works.

Read Power bi treemap – How to use

Power BI Measure sum by group

Here we will see how to create a measure that retuns the sum by groups.

Example-1:

For example, we are going to use the product table having no. of user, that we created previously.

Now we will create a measure, that will group all the product and return the sum of no. of user:

grouping measure = CALCULATE(SUM('Table'[No.of user]),FILTER(ALLSELECTED('Table'),'Table'[Product] = MAX('Table'[Product])))

The above screenshot, we can see that it calculated the sum of each product as a group.

Example-2:

Similarly, we will see how to evaluate sum make it as a group by using Measure with another expression.

Here we have created another table like this:

Let’s create a measure that will make a group for player and calculate it sum:

Measure 4 = CALCULATE(SUM(Results[Points]),ALLEXCEPT(Results,Results[Player]))

You may like the following Power BI tutorials:

• Power bi slicer multiple columns with examples
• Microsoft Power BI Card – How to use
• Power Bi slicer multiple selections
• How to set default value in Power BI Slicer
• Power bi slicer filter another slicer
• How to create a Measure based on Slicer in Power BI
• Power Bi Table Manipulation Functions

Conclusion

From this above Power BI Tutorial, we learned all about Power BI Measure SUM. Also, we discussed:

• How to work with Power BI Measure SUM?
• How to deal with Power BI Measure SUM multiple columns?
• How to calculate Measure SUM using multiple measures in Power BI?
• How to calculate SUM of dates using Power BI Measure?
• How to work with Power BI Measure SUM applying condition?
• How to calculate Power BI Measure SUM using IF function?
• How to use filter function in Power BI Measure SUM?
• How to work with Power BI Measure SUM if column Contains?
• How to calculate the Power BI Measure SUM by group?