Forum Discussion
Calculate median
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.
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
6 Replies
v-frfei-msftCommunity Support
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
- v-frfei-msft
Hi Anonymous,
I used the step partition to insert index by catgory in power query.
Regards,
Frank
- Greg_Deckler
Community Champion
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.
