cancel
Showing results for
Search instead for
Did you mean:
andrewt 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. 1 ACCEPTED SOLUTION

Accepted Solutions 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

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

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

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

Highlighted
andrewt Regular Visitor

## Re: MEDIAN() calculation giving strange results

Oh, ok. That makes sense. Thank you! Community Support Team

## Re: MEDIAN() calculation giving strange results

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.
PriyankaL 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