Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

 

 

 

 

Need Help
  • dax's avatar
    dax
    6 years ago

    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's avatar
    dax
    6 years ago

    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.

4 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        dax
        Icon for Community Support rankCommunity Support
        6 years ago

        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.