Average based on month selection.

Hi,

After a lot test and research, i have two ways to solve your issue:

One:

Create a check column in table 'Member2':

Check = IF(Members2[Current Units]=BLANK(),1,0)

Then create a measure:

Measure = 
VAR a =
    FILTER (
        SUMMARIZE (
            Members2,
            Members2[Year],
            Members2[Membership Type],
            "Check", SUM ( Members2[Check] ),
            "Units", CALCULATE (
                SUM ( Members2[Current Units] ),
                FILTER ( Members2, VALUE ( Members2[Month] ) = SELECTEDVALUE ( Dates[Month] ) )
            )
        ),
        Members2[Membership Type] = "Adult 18+"
            || Members2[Membership Type] = "BH Staff Adult"
    )
RETURN
    SUMX ( a, [Units] )
        / COUNTROWS ( FILTER ( a, [Check] = 0 && [Units] <> 0 ) )

The result shows:

Two:

Change your original YearCount measure to this:

YearCount = CALCULATE(DISTINCTCOUNT('Members2'[Year]),NOT(ISBLANK('Members2'[Current Units])))

Create a measure:

Measure 2 = IF(ISINSCOPE(Members2[Year]),[Units],[Divided Units])

Add this measure to the matrix visual, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi