SUMIFS Equivalent in DAX

Description

In Excel, SUMIFS sums values based on multiple criteria. In DAX, this can be achieved using the combination of CALCULATE and SUM functions.

DAX Formula

Below is the DAX formula to create a SUM with multiple conditions (equivalent to SUMIFS in Excel):

= CALCULATE(
    SUM(Table[SumColumn]),
    Table[CriteriaColumn1] = "Criteria1",
    Table[CriteriaColumn2] = "Criteria2"
)

Explanation

• CALCULATE( [expression] , [filter1] , [filter2] , ... ): The CALCULATE function evaluates the given expression (in this case, SUM(Table[SumColumn])) in a context modified by the given filters.
• SUM( Table[SumColumn] ): This function sums up all the values in SumColumn.
• Table[CriteriaColumn1] = "Criteria1": This filter ensures that the sum is calculated only for the rows where CriteriaColumn1 matches "Criteria1".
• Table[CriteriaColumn2] = "Criteria2": This filter further narrows down the rows to those where CriteriaColumn2 matches "Criteria2".

Practical Example

Assume you have a table named SalesData with columns Revenue, Region, and ProductCategory. To calculate the total revenue for sales in the "North" region and the "Electronics" product category, the DAX formula would be:

= CALCULATE(
    SUM(SalesData[Revenue]),
    SalesData[Region] = "North",
    SalesData[ProductCategory] = "Electronics"
)

Explanation of Practical Example

• SUM(SalesData[Revenue]): Sums the Revenue column.
• SalesData[Region] = "North": Filters the rows to include only those where the Region is "North".
• SalesData[ProductCategory] = "Electronics": Further filters the rows to include only those where the ProductCategory is "Electronics".

By using this formula, you ensure that only the revenues meeting both criteria are summed.

For further learning on DAX, consider exploring courses available on the Enterprise DNA platform.