Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Median Calculated Column using Dax

Hello All,   I have written measure for Median by using the below formula.   _Median = CALCULATE(MEDIAN('Emp Master'[Emp Count]),ALLSELECTED('Emp Master')) Now when i filter by SBU and PRACTICE ...
General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

Anonymous

 

I can repro your issue. And it seems like this issue is related to the MEDIAN() function in calculated column. I have reported this issue internally. If I got any response, I will come back and tell you.

 

Thanks,
Xi Jin.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks for the reply v-xjiin-msft

     

    Please give a solution as soon as possible..

     I ll be waiting for the reply..

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Community Support
      8 years ago

      Hi Anonymous,

       

      When the underlying column is of data type Whole Number, MEDIAN function returns a variant data type because it may return a Whole Number when there is no interpolation or a Decimal Number when there is interpolation. While measures can be of variant data type, calculated columns must be of a single data type, hence the error. To force MEDIAN to always return Decimal Number, change the expression to MEDIANX(Table1, [Column2] * 1.0).

       

      Best Regards,
      Qiuyun Yu

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thanks for the reply v-qiuyu-msft.

        Median forumal worked.

         

        But as i mentioned above in my question, i am grouping the median by grade here.

        When i used the same formula but in calculated column it giving me wrong result. 

        Measure Forumula:-

         

        _Median By Measure = CALCULATE(MEDIAN('Emp Master'[Count]),ALLSELECTED('Emp Master'))

        Calculated Column Formula:- 

         

        _Median By Cal Col = CALCULATE(MEDIANX('Emp Master','Emp Master'[Count]*1.0),ALLSELECTED('Emp Master'))

        Output:-

         

         

        Can you please help me with this...