DAX Formula for Summing Max Filtered Rows

DAX Formula for Finding Max of Filtered Rows and Summing Them

Requirement

Create a DAX formula that finds the maximum values of filtered rows and adds them together.

Solution

The solution involves the following steps:

1. Filtering the desired rows.
2. Finding the maximum value within the filtered context.
3. Summing up these maximum values.

Below is the precise DAX formula to achieve this:

MaxAndSum = 
SUMX(
    FILTER(
        YourTable,
        [YourCondition] // Replace with your actual filtering condition
    ),
    CALCULATE(
        MAX(YourTable[YourColumn])
    )
)

Explanation

• FILTER function: Filters the rows of YourTable based on the given YourCondition.
• MAX function: Computes the maximum value within the filtered context.
• SUMX function: Iterates over the filtered table and sums up the maximum values.

Practical Example

Assume you have a table called Sales with columns Region, Product, and SalesAmount. You want to find the maximum SalesAmount for each region where Product is "Widget", and then sum these maximum values across all regions.

1. Filter: Filter the rows where Product is "Widget".
2. Max: Find the maximum SalesAmount for the filtered rows within each region.
3. Sum: Sum these maximum values.

Below is the corresponding DAX formula:

MaxAndSumSales = 
SUMX(
    VALUES(Sales[Region]),
    CALCULATE(
        MAXX(
            FILTER(
                Sales,
                Sales[Product] = "Widget" && Sales[Region] = EARLIER(Sales[Region])
            ),
            Sales[SalesAmount]
        )
    )
)

Explanation of Example

• VALUES(Sales[Region]): Generates a table of unique regions.
• FILTER function inside MAXX: Filters the rows for each region where Product is "Widget".
• MAXX function: Finds the maximum SalesAmount within the filtered context for each region.
• SUMX function: Sums up these maximum values across all regions.

Conclusion

This DAX formula provides a powerful way to address complex data analysis scenarios by combining filtering, aggregation, and iteration operators. For more in-depth learning and advanced scenarios, consider exploring courses on the Enterprise DNA Platform.