MEDIAN() calculation giving strange results

[Anonymous]

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.  

[Anonymous]

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

[OwenAuger · Accepted solution]

Hi @Anonymous

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

[Anonymous]

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? 

[OwenAuger]

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

[Anonymous]

Oh, ok. That makes sense. Thank you!

[v-chuncz-msft]

@Anonymous,

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

[Sudhavi_84]

Hi,

Is Median has been solved? I am getting similar issue.

I am getting correct Total and Average but for Median, it is giving me average of second and third value instead of third value.

Average is working good 

Average = AVERAGEX(VALUES(Mstone[Project]),[My Measure]) with no issues.

Please see the below link, I have created data model with measures in one drive link from below forum. Please help me to fix this MedianX issue.

https://community.powerbi.com/t5/Desktop/MedianX-is-not-working-Please-Help/m-p/1908928#M730615

Thank you in Advance.

[OwenAuger]

Hi @Sudhavi_84 

Thanks for the question - I found your PBIX on the other post.

The short explanation for what is going on here:

• Blank values are included in the MEDIANX calculation (and BLANK is treated as equal to zero)
• Blank measure results are hidden from visuals by default.

Because of this, your Median measure ends up iterating over 6 values of Project, including ABCD, even though My Measure is blank for ABCD.

The result is that MEDIANX takes the median of 6 values, the result being the mean of the 3rd and 4th values.

Here is a screenshot of your table with My Measure displayed, with Project set to "Show items with no data", which shows the ABCD row where all measures return Blank:

If you want to exclude Blank values from the MEDIANX calculation, one solution would be to write a measure like this:

Median Fixed = 
VAR ProjectMeasure = 
    FILTER ( 
        ADDCOLUMNS ( 
            VALUES ( Mstone[Project] ),
            "@MyMeasure", [My Measure]
        ),
        NOT ISBLANK ( [@MyMeasure] )
    )
RETURN
    MEDIANX (
        ProjectMeasure,
        [@MyMeasure]
    )

 Regards,

Owen

[Sudhavi_84]

I think I found it. by replacing MedianX with CountX.. WIll update you if I fixed it:-)

[OwenAuger]

That's good - let me know if you do need further help 🙂

[Sudhavi_84]

All good @OwenAuger , It is working. Thank you again:-)

[Sudhavi_84]

Hi @OwenAuger 

Please can I have one more help. How can I get one more measure like for example I would like to add one more measure as If project has total value as >=0 or any value then it should show as 1 and then it need to acheive total at bottom like Count of projects which has value

As example ABCD should have blank and other projects should have 1 and Grand total should 5

Sorry can you help please

I wrote a measure as it is giving 1 for each project but total is also coming as 1 instead of 5

Something like this please I would like to achieve

[OwenAuger]

Hi @Sudhavi_84 

Apologies, I missed this message when you first posted it!

I would probably write something like

Projects with value = 
COUNTROWS (
    FILTER ( 
        VALUES ( Mstone[Project] ),
        NOT ISBLANK ( [My Measure] )
    )
)

Regards,

Owen

[Sudhavi_84]

This is good. I will use this then:-) Thank you again @OwenAuger 

[Sudhavi_84]

You are Amazing. I am struggling from last 1 day to acheive this. 

Thank you very much for your help on this. It's working.

I want to click on "Accept Solution" but couldn't find it. 

And also I agree with MedianX it is taking Blank also as consideration as you said.

Out of curioisty,  for average as well it should do the same right? it should take blank as zero and average should come differently.

Why for average it is doing correctly and not including blank values?

Does it mean Blank values will only include for Median but not for average?

Thank you again for your help 

[OwenAuger]

You're welcome 🙂

It turns out that AVERAGEX actually ignores blanks, which I agree means the functions a bit inconsistent.

Check out https://dax.guide for some commentary/examples for these functions.

Regards,

Owen

[Sudhavi_84]

Hi @OwenAuger ,

Could you please help me on this issue.

https://community.powerbi.com/t5/Desktop/Projects-missing/m-p/1928867#M735790

Thank you:-)

[Sudhavi_84]

Hi @OwenAuger , I explained more clearly for my query in

Please let me know if it is not clear. https://community.powerbi.com/t5/Desktop/Projects-missing/m-p/1928867#M735790

[Sudhavi_84]

Perfect Thank you very much @OwenAuger . I will remember that.