cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrewt Regular Visitor
Regular Visitor

MEDIAN() calculation giving strange results

 

In the right-most column in the screenshot provided, I have 4 values:

4.0,

0.2,

0.2,

3.1

 

The result I expect for the MEDIAN of those numbers is 0.2, but PowerBI gives a result of 1.6.  The rows themselves are not summarized, e.g. the actual value of each row is 4.0, 0.2, 0.2, and 3.1.  I don't see how 1.6 could be the median result of these 4 values.  

 

 

MedianExample.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: MEDIAN() calculation giving strange results

Hi @andrewt

The median of an even number of observations is usually defined as the mean of the two middle values. Excel and Power BI's MEDIAN functions both follow this convention.

 

In your example, with four observations, the median would be the mean of 0.2 and 3.1 which is approximately 1.6. 

 

If you wanted a modified median that always picks the lower of the two middle values, you could write some DAX to achieve that.

 

Regards,

Owen

 



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

Proud to be a Datanaut!




6 REPLIES 6
Super User
Super User

Re: MEDIAN() calculation giving strange results

Hi @andrewt

The median of an even number of observations is usually defined as the mean of the two middle values. Excel and Power BI's MEDIAN functions both follow this convention.

 

In your example, with four observations, the median would be the mean of 0.2 and 3.1 which is approximately 1.6. 

 

If you wanted a modified median that always picks the lower of the two middle values, you could write some DAX to achieve that.

 

Regards,

Owen

 



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

Proud to be a Datanaut!




andrewt Regular Visitor
Regular Visitor

Re: MEDIAN() calculation giving strange results

There are 4 values in the list. I don't think I understand why MEDIAN would return an average of the bottom two values. Shouldn't it return the mean of the middle two values? 

Super User
Super User

Re: MEDIAN() calculation giving strange results

Oh, median always operates on a sorted list, so it's the average of the middle two values after sorting.


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

Proud to be a Datanaut!




andrewt Regular Visitor
Regular Visitor

Re: MEDIAN() calculation giving strange results

Oh, ok. That makes sense. Thank you!

Community Support Team
Community Support Team

Re: MEDIAN() calculation giving strange results

@andrewt,

 

By the way, you may help accept solution. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
PriyankaL Frequent Visitor
Frequent Visitor

Re: MEDIAN() calculation giving strange results

I am facing the same issue.The values in excel is different than in power BI. It is not giving the average of two mid values also