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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-qiuyu-msft v-xjiin-msft can you please suggest me about this..

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        Anonymous

         

        That's weird. I have made some test based on your sample data. Use Enter Data directly to create a source table. However the measure and calculated column with same expression return the same result.

         

        Please refer:

         

         

        Thereby, I think the issue is not related to the expression. Maybe it exists in other parts of your report. Please verify this.

         

        Thanks,
        Xi Jin.