SUMIFS in Power BI using DAX CALCULATE

The following guide will show how we can perform a SUMIFS in Power BI using DAX Calculate. One of the first things you may notice when working with Power BI versus Excel is that you cannot reference a single cell or range of cells. We can perform the equivalent function in Power BI by using the CALCULATE() function and filtering column data to achieve the desired result.

SUMIFS Using DAX CALCULATE():

CALCULATE(expression,filter1,filter2…)

The calculate function in Power BI is quite powerful in that it can evaluate an expression along with multiple filtering criteria. This is how we can perform useful calculations in Power BI when we cannot reference a specific cell or range of cells like we do in Excel. We use the filtering criteria to narrow down the data we want to perform the calculation on and in combination with the various visualizations available this will allow for powerful data analysis within Power BI models.

CALCULATE() Reference Documentation

Example Create SUMIFS in Power BI:

With the following dataset we can perform a SUMIFS on the Product ID:

Product IDCountry Volume 
Product 4Canada      6,988
Product 2United States      2,717
Product 8Mexico      2,731
Product 4Canada      1,885
Product 3United States      3,094
Product 9Mexico      8,626
Product 2Canada      5,271
Product 5United States      3,919
Product 7Mexico      7,145
Product 3Canada      8,857
Product 2United States      7,037
Product 1Mexico      8,046
Product 10Canada      9,812
Product 9United States      9,547
Product 2Mexico      5,647
Product 1Canada      5,797
Product 6United States      2,610
Product 10Mexico      4,141
Product 7Canada      5,786

We will calculate the sum of Product 3 by creating a new measure with the following syntax:

CALCULATE(SUM(Volume),Product ID = "Product 3"
Excel SUMIFS in Power BI using DAX CALCULATE

Result:

Excel SUMIFS in Power BI using DAX CALCULATE
Excel SUMIFS in Power BI using DAX CALCULATE

If multiple criteria is required then we can another filter argument:

CALCULATE(SUM(Volume),Product ID = "Product 3",Country = "Canada"
Excel SUMIFS in Power BI using DAX CALCULATE with multiple criteria

Result:

Excel SUMIFS in Power BI using DAX CALCULATE with multiple criteria

This is a very simple example but illustrates how we can perform the equivalent of an Excel SUMIFS in Power BI. With very large data sets the calculate function will become extremely useful in displaying the desired results within the Report view.