How does one filter out zeros/nulls when using MEDIANX and FILTER combo?

[RiskyBiscuts]

This has been a real headache, I have been trying to create a calculated column (has to be a calc. col.) that gets me the median value for each "UnitType". The challenge I am encountering is that calculation is accounting for the 0 or null values in the "Dev/Known" column, so the median is either inaccurate or I get a 0 value for an answer (due to all the zeros and nulls). What can I do to get around this?

Median Dev/Known =

MEDIANX

(

     FILTER(

         ALL('Project Data'),[UnitType] = EARLIER([UnitType])),

[Dev/Known]

)

I have part of the table below. In excel this is rather easy to do, PBI, not so much.

Table to be analyzed

Thank you!

[edhans · Accepted solution]

Will this work?

Median Dev/Known =
VAR varUnitType = [UnitType]
RETURN
    MEDIANX(
        FILTER(
            ALL( 'Project Data' ),
            [UnitType] = varUnitType
                && NOT [Dev/Known]
                    IN {
                        0,
                        BLANK()
                    }
        ),
        [Dev/Known]
    )

It will filter the table to exclude rows where the Dev/Known field is zero or null. 
I also got rid of the Earlier function. Pet peeve of mine since variables came out. 😉

[RiskyBiscuts]

This is exactly what I was looking for! Thank you!

[edhans]

Great @RiskyBiscuts - glad to help.