Find Difference Between Median of a Column and Rows of Another Column

[Anonymous]

I have a simple Power BI query that has produced this data.

I want to add another column (call it DifFrmMedian) that calculates the difference between the median of the values in the ratio column and the values in the ratio column, i.e. medain of ratio column - value in ratio column.

In an Excel worksheet I would just calculate the median by =median([ratio]) and place it in a cell, say A1. I would then add a column to my table with the formula =A1-[ratio] to get the difference between the median of the ratio column and the value of each cell in the ratio column. Obviously, I am new to DAX. I tried creating a measure for the median of the ratio column then using it in a calculated column, but no luck there...

Any help greatly apprecated.

Regards,

Rick

[MFelix · Accepted solution]

Hi  @Anonymous ,

Add a blank step after your last step and then use the following code:

= Table.AddColumn(#"Changed Type", "Group", each [ratio] - List.Median(#"Changed Type"[ratio]))

the #"Change Type" should be altered to the name of the last step before this one.

See example file attach,

Regards,

MFelix

[ScotsSailor]

Do you have to sort the values highest to lowest (or lowest to highest) for the median function to work correctly?

[Anonymous]

Thanks very much! Using just the sample data the measure MEDIAN(tablename[ratio]) and the column MEDIAN(tablename[ratio]) produce the same result. With the actual data (574 records) the measure produces .8306 while the column produces .80870. Using an Excel formula (=AGGREGATE(12,4,[Ratio]), I verified the median was .8306.  The solution you provided is using a median of .80870.  What could cause the difference in the median results? Sorry I was slow to circle back to this. I ran into this issue and did not devote the time until now. Thanks again for the help!!