Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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?
Oh, ok. That makes sense. Thank you!
@Anonymous,
By the way, you may help accept solution. Your contribution is highly appreciated.
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
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.
Hi @Sudhavi_84
Thanks for the question - I found your PBIX on the other post.
The short explanation for what is going on here:
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
I think I found it. by replacing MedianX with CountX.. WIll update you if I fixed it:-)
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
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
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
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
Hi @OwenAuger ,
Could you please help me on this issue.
https://community.powerbi.com/t5/Desktop/Projects-missing/m-p/1928867#M735790
Thank you:-)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |