Forum Discussion

saturation's avatar
saturation
Frequent Visitor
6 years ago
Solved

Find Median value based on zip code / data file

Hi - I'm trying to get the median and mean rent ranges for each of the # of bedroom "sections" from the spreadsheet below.

 

In the sample spreadsheet, rows 2-82 are all for 1 zip code (zcta - column A). Then, in column D, for every # of bedrooms, i.e., cell E2 ("1 bedroom"), then the next 26 rows show the rent ranges for 1 bedrooms. After that, it goes onto 2 bedroom rent ranges (and that section of additional 26 rows).  

 

Data here:  https://docs.google.com/spreadsheets/d/e/2PACX-1vSf_v_qBJeJN0-F-0TYNIKe-iPHllsRHtWiYHOJSdxoj2UxYorneql_3MjdtxyOwKvaQ8hegtlTNsRB/pubhtml

 

Out of that data, for each rent range (1 bedroom, 2 bedrooms, etc), I'm trying to get

1 bedroom: $750 to $799 (because it has the most "count" from num_estimate)

2 bedroom: $1,000 to $1,249 (because it has the most "count" from num_estimate)

 

So, I'm hoping I can get the data to look similar to this

 

Zip code  |  # of rooms   |   Best Rent Range

60002      |    1                 |   $750-$799

60002      |    2                 |    $1,000 to $1,249

60002.....       3   etc. 

60004     |     1                 |  $1,000 to $1,249

 

Can anyone help here? Or if you have better suggestions or more accurate ones, let me know!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.