Median value not same as Excel

[Anonymous]

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:

Class	marks	Roll No
A	12	1
A	45	14
B	12	13
D	45	17
C	24	69
B	26	40
B	12	17
C	15	13
D	67	66

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.

[dax · Accepted solution]

Hi @Anonymous , 

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

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

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.

[dax · Accepted solution]

Hi @Anonymous , 

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

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

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.

[Anonymous]

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 · Accepted solution]

Hi @Anonymous , 

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

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.

[Anonymous]

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