Here is a sample of my data
| UNIQ_ID | ORGDATA_CPY_NAME | POS_CODE | EMP_COST |
| ID1 | Company A | Job 1 | 184975 |
| ID2 | Company B | Job 2 | 171233 |
| ID3 | Company B | Job 2 | 183293 |
| ID4 | Company B | Job 2 | 92473 |
| ID5 | Company C | Job 2 | 229272 |
| ID6 | Company D | Job 2 | 148892 |
| ID7 | Company C | Job 2 | 204673 |
| ID8 | Company B | Job 2 | 182699 |
| ID9 | Company B | Job 2 | 116287 |
| ID10 | Company E | Job 2 | 176553 |
| ID11 | Company E | Job 2 | 239427 |
| ID12 | Company C | Job 3 | 168012 |
| ID13 | Company C | Job 3 | 167761 |
| ID14 | Company D | Job 4 | 137955 |
| ID15 | Company B | Job 5 | 245209 |
| ID16 | Company E | Job 6 | 234693 |
| ID17 | Company F | Job 7 | 222599 |
| ID18 | Company B | Job 8 | 112344 |
| ID19 | Company B | Job 8 | 113580 |
| ID20 | Company B | Job 8 | 139393 |
| ID21 | Company E | Job 8 | 107764 |
| ID22 | Company G | Job 9 | 199456 |
| ID23 | Company G | Job 9 | 130194 |
| ID24 | Company F | Job 9 | 78545 |
| ID25 | Company F | Job 9 | 217730 |
| ID26 | Company H | Job 9 | 186181 |
| ID27 | Company H | Job 9 | 89719 |
| ID28 | Company H | Job 9 | 225200 |
| ID29 | Company H | Job 9 | 123443 |
| ID30 | Company H | Job 9 | 95538 |
I tried multiple variations and ran into the same issue every time.
Table_CompaRatio =
VAR SummaryTable =
SUMMARIZE (
'POSDATA',
'POSDATA'[POS_CODE],
"DistinctCount_ORGDATA_CPY_NAME", DISTINCTCOUNT('POSDATA'[ORGDATA_CPY_NAME]),
"Count_UNIQ_ID", COUNT('POSDATA'[UNIQ_ID]),
"Median_EMP_COST", MEDIANX (
FILTER (
ALL('POSDATA'),
'POSDATA'[POS_CODE] = EARLIER('POSDATA'[POS_CODE]) &&
DISTINCTCOUNT('POSDATA'[ORGDATA_CPY_NAME]) > 2 &&
COUNT('POSDATA'[UNIQ_ID]) > 3
),
'POSDATA'[EMP_COST]
)
)
RETURN
CALCULATETABLE (
SELECTCOLUMNS (
SummaryTable,
"POS_CODE", 'POSDATA'[POS_CODE],
"DistinctCount_ORGDATA_CPY_NAME", [DistinctCount_ORGDATA_CPY_NAME],
"Count_UNIQ_ID", [Count_UNIQ_ID],
"Median_EMP_COST", [Median_EMP_COST]
),
ALLEXCEPT('POSDATA', 'POSDATA'[POS_CODE])
)
Which brought up this error message:
"The DAX expression for calculated table 'Table_CompaRatio' results in a variant data type for column 'Median_EMP_COST'. Please modify the calculation such that the column has a consistent data type."
I have ensured that there are no blanks and all EMP_COST values are numeric.
I'm using Version: 2.126.1261.0 64-bit (February 2024)