Calculate median

[Anonymous]

Hi,

I have a dataset.

Config ID	Is similar	part gp desc	item	Total Profit	Output Column -Median
ABC-12345	Yes	gp1	item1	870	769
ABC-12345	Yes	gp2	item2	947	769
ABC-12345	Yes	gp3	item3	844	769
ABC-12345	Yes	gp4	item4	844	769
ABC-12345	No	gp5	item5	769	769
ABC-12345	No	gp6	item6	146	769
ABC-12345	No	gp7	item7	954	769
ABC-12345	No	gp8	item8	455	769
ABC-12345	No	gp9	item9	455	769
XYZ-12345	Yes	gp10	item10	560	888
XYZ-12345	Yes	gp11	item11	814	888
XYZ-12345	Yes	gp12	item12	885	888
XYZ-12345	Yes	gp13	item13	717	888
XYZ-12345	No	gp14	item14	888	888
XYZ-12345	No	gp15	item15	487	888
XYZ-12345	No	gp16	item16	152	888
XYZ-12345	No	gp17	item17	881	888
XYZ-12345	No	gp18	item18	881	888

I need to calculate median at Config level which is described in the 'output column - median'

2 issues that im facing is :

1. Im try to use median formula in calculate but not sure which dax to use. All,Allexcept etc

2. When i put median to the value shelf of a table in powerbi it summarizes by default. I dont want it to summarize as median is already summarized. Dont summarize option gives more than 1 row of result.

[v-frfei-msft · Accepted solution]

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

[Anonymous]

HI Frank,

What does the m code do in the partition block ?

If you can explain me the purpose and if I can acheive the same ia UI, it would help as my actual data source is amazon redshift

[v-frfei-msft]

Hi @Anonymous,

I used the step partition to insert index by catgory in power query.

Regards,

Frank

[Anonymous]

So in my original source code do I add the same M code that I see in your pbix file. What will change?

If you could explain the structure/logic of the code that would be helpful

[v-frfei-msft]

Hi @Anonymous,

Please check the document about Table.AddIndexColumn and Table.Group in Power query.

https://www.powerquery.io/table/table.addindexcolumn

https://www.powerquery.io/table/table.group

Regards,

Frank

[Greg_Deckler]

Well if you really want a column versus a measure, then you *should* be able to do this:

Column =

MEDIANX(FILTER(ALL('Table2'),[Config ID] = EARLIER([Config ID])),[Total Profit])

However, I am getting an error around a variant data type returned, which should not be the case since it should return a decimal number. Seems like a bug so going to call in @marcorusso to see if that is true.