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.
With the following dataset we can perform a SUMIFS on the Product ID:
Product ID
Country
Volume
Product 4
Canada
6,988
Product 2
United States
2,717
Product 8
Mexico
2,731
Product 4
Canada
1,885
Product 3
United States
3,094
Product 9
Mexico
8,626
Product 2
Canada
5,271
Product 5
United States
3,919
Product 7
Mexico
7,145
Product 3
Canada
8,857
Product 2
United States
7,037
Product 1
Mexico
8,046
Product 10
Canada
9,812
Product 9
United States
9,547
Product 2
Mexico
5,647
Product 1
Canada
5,797
Product 6
United States
2,610
Product 10
Mexico
4,141
Product 7
Canada
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"
Result:
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"
Result:
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.
