Dax Calculated column with calculation

[obriaincian]

Hi All,

My dataset looks like this

Company	ID	Status	Submitted
Apple	11	TD Complete
Apple	11	TD Complete	Submitted
Microsoft	22	TD Complete
Google	333	TD Complete
Google	333	TD Complete
Google	333	TD Complete
Amazon	44	TD Complete
Amazon	44	TD Complete
Amazon	44	TD Complete
Amazon	55	TD Complete
Amazon	66	TD Complete	Submitted

I now want to create a calculated table,

I want this table to be grouped by company and show the count of DISTINCT IDS that are Completed.

The second part is to then have another column in the table that shows the DISTINCT count of IDS that are Completed MINUS DISTINCT count of IDS that are submitted.

RESULT

Company	Complete	Complete -Submitted
Apple	1	0
Microsoft	1	1
Google	1	1
Amazon	3	2

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

New Table =
ADDCOLUMNS (
    DISTINCT ( Data[Company] ),
    "Complete", CALCULATE ( COUNTROWS ( DISTINCT ( Data[ID] ) ), Data[Status] = "TD Complete" ),
    "Complete-Submitted",
        CALCULATE ( COUNTROWS ( DISTINCT ( Data[ID] ) ), Data[Status] = "TD Complete" )
            - CALCULATE (
                COUNTROWS ( DISTINCT ( Data[ID] ) ),
                Data[Status] = "TD Complete",
                Data[Submitted] = "Submitted"
            )
)