Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

Untitled 1.jpg

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

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Anonymous
Not applicable

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!! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.