Calculate median

Hi Anonymous,

Please check the following steps as below.

1. We should insert an index column ranked by [Config ID] column in Power query. You can refer to the M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdGxCsJADAbgd7m5QlOTu9yo7s7W0rGIgyjo+2NzJAWpvSz5M3wcOf5hCIfjaQfdHik0oZ/e87y9YJ73z/SQ5NTOM8Ucxua/7lRLZkyO3quWZERHo2qs6vOzYFJMyqo4KpYEjHWcFEtmcs5gxZJIVMdZcV7hS39dF9NaM7JQlMnM235pslQJ6HnrsizM5HlrsywJ0obXz4LVuRxS51ZoWZC9163SsgB1DrdS9WpwuNUK/MvHLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Config ID" = _t, #"Is similar" = _t, #"part gp desc" = _t, item = _t, #"Total Profit" = _t, #"Output Column -Median" = _t]),
    Partition = Table.Group(Source, {"Config ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Is similar", "part gp desc", "item", "Total Profit", "Output Column -Median", "Index"}, {"Partition.Is similar", "Partition.part gp desc", "Partition.item", "Partition.Total Profit", "Partition.Output Column -Median", "Partition.Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Partition",{{"Partition.Output Column -Median", Int64.Type}, {"Partition.Total Profit", Int64.Type}})
in
    #"Changed Type"

2. Then we can create a measure to get the result as we need.

_Output Column -Median = var _round=ROUND(CALCULATE(COUNTROWS('table'),ALL('table'),VALUES('table'[Config ID]))/2,0)
return
CALCULATE(MAX('table'[Partition.Total Profit]),FILTER(ALLEXCEPT('table','table'[Config ID]),'table'[Partition.Index]=_round)
)

For more details, please check the pbix as attached.

Regards,

Frank