cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Median value not same as Excel

 

I am pretty new to power bi.

 

So I am trying to calculate median and have been getting difference in values when I calculate edian using excel and when I use power bi to do the same.

 

An example of my  table is as follows:

 

ClassmarksRoll No
A121
A4514
B1213
D4517
C2469
B2640
B1217
C1513
D6766

 

Now I plan at getting median by class. My formula works however there is a difference i result when I compare the same with median from excel.(again i am not talking about the total median but individual medians by class). this diffrence is only when the number of data points(in thiscase count of marks in a class) is odd. Somehow power bi take the average of thee middle and middle +1 value.

 

 

 

 

2 ACCEPTED SOLUTIONS
dax
Community Support
Community Support

Hi @Anonymous , 

I test this in my emviornment, I  get result like below

Measure = CALCULATE(MEDIAN('Table'[marks]))

689.PNG

688.PNG

The results are the same, and you could refer to https://www.mathsisfun.com/median.html  for logic.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

dax
Community Support
Community Support

Hi @Anonymous , 

I have shown DAX in above post , you could refer to it, and below is expression to calculate median in excel

690.PNG

If you have other fields, you also could add it in below expression

 CALCULATE(MEDIAN('Table'[marks]), ALLEXCEPT('Table','Table'[Class]))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
dax
Community Support
Community Support

Hi @Anonymous , 

I test this in my emviornment, I  get result like below

Measure = CALCULATE(MEDIAN('Table'[marks]))

689.PNG

688.PNG

The results are the same, and you could refer to https://www.mathsisfun.com/median.html  for logic.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Could you share the dax used. I do the logic behind getting the medain ut having problems on what result my dax throws me.

dax
Community Support
Community Support

Hi @Anonymous , 

I have shown DAX in above post , you could refer to it, and below is expression to calculate median in excel

690.PNG

If you have other fields, you also could add it in below expression

 CALCULATE(MEDIAN('Table'[marks]), ALLEXCEPT('Table','Table'[Class]))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Thanks a lot..Actually i had a flawed logic depening on how my table was build..Your small table kinda helped me understand the same ...Thanks a lot...

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors