Find Median value based on zip code / data file

Hi saturation 

In Edit queries,

Added Conditional Column, then Filled Down.

#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "bedroom category", each if Text.Contains([stub], "bedroom") then [stub] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"bedroom category"})

Close&&apply

Create measures

Measure =
CALCULATE (
    SUM ( Sheet1[num_estimate] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[zcta], Sheet1[bedroom category] ),
        Sheet1[stub] = MAX ( Sheet1[stub] )
            && Sheet1[stub] <> "With cash rent:"
            && Sheet1[stub] <> "Less than $100"
            && SEARCH ( "bedroom", MAX ( Sheet1[stub] ), 1, 0 ) = 0
    )
)


Measure 2 = MAXX(ALLEXCEPT(Sheet1,Sheet1[zcta],Sheet1[bedroom category]),[Measure])

Measure 3 = IF([Measure]=[Measure 2],MAX(Sheet1[stub]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.