Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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, 😊
I know this is old but a simpler fix is converting the source data to decimal numbers.
@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.
Thanks for the reply @v-xjiin-msft
Please give a solution as soon as possible..
I ll be waiting for the reply..
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
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
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.
@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'))
@v-xjiin-msft @v-qiuyu-msft Can you please give any suggestion regarding this..
It will be lot helpful for me.
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.