Median Calculated Column using Dax

[Anonymous]

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.

[S184019]

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, 😊

[jfallt]

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

[v-xjiin-msft]

@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]

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]

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]

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]

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

[v-xjiin-msft]

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

[Anonymous]

@v-xjiin-msft Thanks for the reply.

I think you didnt checked with mulitple grades.

I too used the same formula

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

[Anonymous]

@v-xjiin-msft @v-qiuyu-msft Can you please give any suggestion regarding this..

It will be lot helpful for me.

[v-xjiin-msft]

Hi @Anonymous,

Sorry for delayed reply.

But I'm still getting the right result even with multiple grades. Thereby could you please share us your report file (pbix) if possible?

Thanks,
Xi Jin.