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 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:
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.