SUMMARIZE table with filter

[LCTurner]

Hi all,

I have some salary data with these variables:
UNIQ_ID = a unique identifier for each row of data (i.e. each employee)
ORGDATA_CPY_NAME = the name of the organisation they work for
POS_CODE = the position code or job code they are assigned to
EMP_COST = their total employment cost (salary)

I want to create a table called Table_CompaRatio that I can use as the basis for other calculations using DAX syntax but I cannot seem to get it to work.
I want to group by POS_CODE and display the distinct count of ORGDATA_CPY_NAME, count of UNIQ_ID and median of EMP_COST.

I also want to filter the results so it only includes rows for POS_CODEs where distinct count of ORGDATA_CPY_NAME >2 and count of UNIQ_ID >3.

I have tried multiple different ways of doing it but keep getting different errors.

I am very new to Power BI so have been using a chat GPT type AI to assist creating the syntax.

I tried this syntax:

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 also tried this:

Table_CompaRatio =

CALCULATETABLE(SUMMARIZE(POSDATA,POSDATA[POS_CODE],"CPYCOUNT",DISTINCTCOUNT(POSDATA[ORGDATA_CPY_NAME]),"INCCOUNT",count(POSDATA[UNIQ_ID]),"EC_MED",MEDIAN(POSDATA[CMP_EC])),DISTINCTCOUNT(POSDATA[ORGDATA_CPY_NAME])>2, COUNT(POSDATA[UNIQ_ID])>3)

But had an error:
"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

I have created the output as a visualisation so I know what the data should look like:

Any help would be appreciated.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[LCTurner]

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

[lbendlin · Accepted solution]

First step:  Create a table visual with implicit measures (and no totals)

Second step: "Borrow" the DAX code from Performance Analyzer

Last step:  cleanup

New Table = SUMMARIZECOLUMNS(
			'Table'[POS_CODE],
			"DistinctCountORGDATA_CPY_NAME", CALCULATE(DISTINCTCOUNT('Table'[ORGDATA_CPY_NAME])),
			"CountUNIQ_ID", CALCULATE(COUNTA('Table'[UNIQ_ID])),
			"MedianEMP_COST", CALCULATE(Value(MEDIAN('Table'[EMP_COST])))
		)

Optional: apply your filters to the new table.

Note:  There is clearly a bug in Power BI resulting in your 

"The DAX expression for calculated table 'New Table' results in a variant data type for column 'MedianEMP_COST'. Please modify the calculation such that the column has a consistent data type."

error.  I worked around it with the VALUE() cast but I shouldn't have to.  If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[LCTurner]

Thanks so much for the explanation.
I will raise the issue on the community page as well.

Thanks!