cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Super User III
Super User III

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

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 Felix


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

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Super User III
Super User III

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

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 Felix


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

Proud to be a Datanaut!




View solution in original post

Anonymous
Not applicable

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

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors