How to SUM by subgroups based on conditional criteria?

Hey Anonymous ,

your last explanation is exactly what is needed to avoid misunderstandings.

First I created a calculated column to extract the year to have a numeric representation of the FY, this makes checking rule 2 more simple:

Giftdata_FiscalYear_Value = RIGHT( 'Gifts'[Giftdate_FiscalYear] , 4)

It is recommended creating this column using Power Query, or even better already in the source system. Nevertheless, I use DAX because because simplicity.

Then I use DAX to create this measure:

Measure = 
var thevalue =
    SUMX(
        FILTER(
            ADDCOLUMNS(
                FILTER(
                    ADDCOLUMNS(
                        SUMMARIZE(
                            'Gifts'
                            , Gifts[Person_Id]
                            , Gifts[Giftdata_FiscalYear_Value]
                        )
                        , "# of entries" , CALCULATE( COUNTROWS( 'Gifts' ), ALL( Gifts[Giftdata_FiscalYear_Value] , Gifts[Giftdate_FiscalYear] ) )
                    )
                    , [# of entries] >= 3 && [Giftdata_FiscalYear_Value] >= 2021
                )
                , "sum of rev" , CALCULATE( SUM(Gifts[Revenue] ) )
            )
            , [sum of rev] >= 60
        )
        , [sum of rev]
    )
return
    IF( ISBLANK( thevalue )
        , 0
        , thevalue
    )

This allows to create this table visual:

Please be aware that showing 0 instead of BLANK (meaning an empty cell) can become costly the larger the table gets.

Hopefully, this provides what you are looking for.

Regards,

Tom