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 and EMP GRADE's, it gets fillter perfectly.

 

 

Now when i write the same formula in CALCULATED COLUMN as below

 

_CAL MEDIAN = CALCULATE(MEDIAN('Emp Master'[EMp Count]),ALLSELECTED('Emp Master'))

I am getting error as

 

Expressions that yield variant data-type cannot be used to define calculated columns.

 

Can anyone please suggest me about this.

General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests

11 Replies

  • 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

  • jfallt's avatar
    jfallt
    Frequent Visitor
    7 years ago

    I know this is old but a simpler fix is converting the source data to decimal numbers.

  • S184019's avatar
    S184019
    Advocate III
    5 years ago

    So no solution on this? 

     CALCULATE(MEDIANX('Emp Master','Emp Master'[Count]*1.0),ALLSELECTED('Emp Master'))

    When I try re-creating this column (not measure), I get an error stating, Expressions that yield variant data-type cannot be used to define calculated columns. 

     

    How do we create a median column??

     

    Thanks, ğŸ˜Š