The SUMIF function does not exist in Power BI however we can replicate this using different functions (DAX).

  • SUMIF is used in Excel to sum values that match a given criteria.
  • CALCULATE is used in Power BI to do the same thing

Quick Guide

Syntax

What you need with speed

SUMIF = CALCULATE ( SUM ( sum_column )  , filter_column = "criteria" )

Best Practice Syntax

Follow best practice to create better reports

Total Sales Measure

Total Sales = SUM ( sum_column )

SUMIF Measure

SUMIF = CALCULATE ( [Total Sales], filter_column = "criteria" )

Worked Example: link to example

Power BI Dashboard Download

DOWNLOAD COMPLETED EXAMPLE HERE

SUMIF Variations

SUMIF all conditions are true

SUMIF = CALCULATE ( [Total Sales], filter_column_1 = "criteria_1", filter_column_2 = "criteria_2" )

Worked Example: link to example

SUMIF any condition is true

SUMIF = CALCULATE ( [Total Sales], filter_column IN {"criteria_1","criteria_2"} )

Worked Example: link to example

SUMIF calculation is true

SUMIF = 
CALCULATE ( [Total Sales], 
FILTER ( ALL ( filter_column_1 ), [Calculation] > criteria) 
)

Worked Example: link to example

SUMIF when ONLY a certain condition is true

SUMIF = CALCULATE ( [Total Sales], 
KEEPFILTERS ( filter_column = "criteria" ) )

Worked Example: link to example

Excel SUMIF

I need to sum values only IF a condition is true, help! (sumif)

We can do this in excel with a single formula:

  • SUMIF: sum the values in a range that meet criteria that you specify
=SUMIF(look_in_this_range, for_this_criteria, sum_these_values)

Learn how to use SUMIF in Excel here

What is a measure

In Power BI formulas are referred to as measures.

They are written in a language called DAX (Data Analysis Expressions).

The layout for a measure is as follows

Name of Measure = Calculation

A calculation would look like this

Name of Measure = SUM ( 'Table Name'[Column Name]  )

Examples

Let’s use an example to illustrate the formula. 

COMPLETED EXAMPLE DOWNLOAD HERE

Relevant Tables and Columns: Product[Brand] and Sales[Sales Amount]

We start by writing the base measure. This will sum our sales column.

Total Sales Measure

Total Sales = SUM ( Sales[Sales Amount]  )

SUMIF Basic

I want to sum all sales where Brand = “Adventure Works“

In Excel this would look like

=SUMIF(brand_column,"Adventure Works",sales_amount_column)

In Power BI we follow the logic below

Total Sales Measure

Total Sales = SUM ( Sales[Sales Amount]  )

I want to return Total Sales where the Brand = Adventure Works

To do this, we use a CALCULATE statement

calculate syntax

Measure Name = CALCULATE ( [Measure], filter_to_apply )

We use CALCULATE to apply a filter to our Brand columns.

SUMIF Measure

SUMIF = CALCULATE ( [Total Sales], 'Product'[Brand] = "Adventure Works" )

This filter sets Brand = Adventure Works and then calculates [Total Sales]

We now have our Basic SUMIF in Power BI

SUMIF all conditions are true

I want [Total Sales] when the Brand is “Adventure Works” and the color is “Red“

Total Sales Measure

Total Sales = SUM ( Sales[Sales Amount]  )

We use CALCULATE to apply filters to our Brand and Color column.

SUMIF Measure

SUMIF = CALCULATE ( [Total Sales]  , 
'Product'[Brand] = "Adventure Works", 'Product'[Color] = "Red" )

This filter sets Brand = Adventure Works AND Color = Red then calculates [Total Sales]

SUMIF any condition is true

I want [Total Sales] when the Brand is “Adventure Works” or “Contoso“

Total Sales Measure

Total Sales = SUM ( Sales[Sales Amount]  )

We use CALCULATE to apply filters to our Brand column.

SUMIF Measure

SUMIF = CALCULATE ( [Total Sales]  , 
'Product'[Brand] IN {"Adventure Works", "Contoso"} )

This can also be written as

SUMIF = CALCULATE ( [Total Sales]  , 
'Product'[Brand] = "Adventure Works" || 'Product'[Brand] = "Contoso" )

This filter sets Brand = Adventure Works OR Contoso then calculates [Total Sales]

SUMIF calculation is true

I want [Total Sales] when the Brand has [Total Sales] more than £10m

Total Sales Measure

Total Sales = SUM ( Sales[Sales Amount]  )

We use CALCULATE to apply filters to our Brand column.

SUMIF Measure

SUMIF = 
CALCULATE ( [Total Sales]  , 
FILTER ( ALL ( 'Product'[Brand] ), [Total Sales]  > 10000000 ) 
)

This is a more complex filter.

The filter calculates [Total Sales] for each Brand

The filter then uses this result to remove Brands with less than £10m from the table of Brands.

[Total Sales] is then finally calculated only for these brands.

SUMIF when ONLY a certain condition is TRUE

You may be faced with the image below when you apply your SUMIF to a table.

I want to use SUMIF, not remove all the other filters in the table!

I only want to show the SUMIF when Brand = “Adventure Works“

Total Sales Measure

Total Sales = SUM ( Sales[Sales Amount]  )

We use CALCULATE to apply filters to our Brand column but surround this with KEEPFILTERS.

SUMIF Measure

SUMIF = CALCULATE ( [Total Sales], 
KEEPFILTERS ( 'Product'[Brand] = "Adventure Works" ) )

This ensures the existing row filters are kept, and hence, not removed.

On each row we effectively check if the Brand is “Adventure Works” AND the “Current Brand“.

Summary

You have effectively learnt how to transfer your knowledge from Excel to Power BI. It wasn’t so bad, was it?! You can now go on to Sum any criteria you wish in Power BI.

COMPLETED EXAMPLE DOWNLOAD HERE