Averagex of a measure with filter

[ttseng]

Hi, 

I have a measure that returns the most recent known attendance of schools called [last_known attendance].

Now I want to know the average of attendace rates so I created the following measure:

Avg_known_attendance = 
      AVERAGEX(DISTINCT(Schools[School_ID]), [Last_known_attendance])

This works great. The only problem is that my dataset has 0 for schools that did not submit attendance rates which skews the overall average. What I want is to be able to do is add a filter to the 'avg_known_attendance'  but only with [last_known_attendance] > 0. This is a seemingly simple but cannot seem to figure out how to filter the measure.

Thanks in advance. 

[mahoneypat · Accepted solution]

Please try this expression to get your result

Average Without Zeros =
VAR summary =
    ADDCOLUMNS (
        DISTINCT ( Schools[School_ID] ),
        "@attendance", [Last_known_attendance]
    )
RETURN
    AVERAGEX (
        FILTER (
            summary,
            [@attendance] > 0
        ),
        [@attendance]
    )

Regards,

Pat

[ttseng]

Thanks @mahoneypat !

Your solution worked perfectly.