Calculate distinct count total at a group level

pmetaliya — Wed, 11 Aug 2021 17:23:15 GMT

Hi,

I'm trying to create a measure to get the "Other" count for each ethnicity but having trouble calculating total distinct count group by Ethnicity. There is 1:M relationship between Person(Ethnicity) and Behavior(Incident ID). 5,928 is a distinct count without ethnicity group and 6,297 is the total (sum of #Incident for each ethnicity) I'm looking for to determine Other. Ex: #Incident for Asian = 103, Other = 6297-103 = 6194.

Expected result for #Incident Other

Ethnicity	# Incident	# Incident Other	TotalAll-Wrong	Desired Total
American Indian or Alaska Native	10	6287	5928	6297
Asian	103	6194	5928	6297
Black or African American	3531	2766	5928	6297
Hispanic/Latino	1464	4833	5928	6297
Native Hawaiian or Other Pacific Islander	9	6288	5928	6297
White	1180	5117	5928	6297

TotalAll-Wrong = CALCULATE(DISTINCTCOUNT('Behavior'[DISCIPLINE_INCIDENT_ID]), ALL(Person[Ethnicity]))

I was able to get 6297 as a total with these formulas but not when the ethnicity filter context was applied.

# Incident All 1 = SUMX( VALUES(Person[Ethnicity Cd]), CALCULATE(DISTINCTCOUNT('Behavior'[DISCIPLINE_INCIDENT_ID])))

# Incident All 2 = VAR TotalGrpbyEthn = SUMMARIZE( 'Person', Person[Ethnicity], "EthnDistinct", DISTINCTCOUNT ('Behavior'[DISCIPLINE_INCIDENT_ID] ) ) RETURN SUMX(TotalGrpbyEthn, [EthnDistinct])

# Incident All 4 = VAR TotalGrpbyEthn = SUMMARIZE( 'Person', Person[Ethnicity], "EthnDistinct", DISTINCTCOUNT ('Behavior'[DISCIPLINE_INCIDENT_ID] ) ) RETURN CALCULATE((SUMX(TotalGrpbyEthn, [EthnDistinct])), ALLEXCEPT(Person, Person[Ethnicity]))

Relationship

I need this to be dynamic to work across multiple other slicers from School and Person tables on the report page. Could you please point me in the right direction?

pbix file with sample data

Re: Calculate distinct count total at a group level

wdx223_Daniel — Thu, 12 Aug 2021 00:33:17 GMT

# Incident = CALCULATE(DISTINCTCOUNT('Behavior'[DISCIPLINE_INCIDENT_ID])) # Incident All 1 = SUMX( VALUES('Person'[Ethnicity]), [# Incident]) # Incident Other 1 = VAR _all=CALCULATE([# Incident All 1],ALL(Person[Ethnicity])) RETURN IF([# Incident All 1],_all-[# Incident All 1])

Re: Calculate distinct count total at a group level

pmetaliya — Thu, 12 Aug 2021 14:18:05 GMT

Wow. Thanks, Daniel. Your measures are working as expected.

topic Re: Calculate distinct count total at a group level in DAX Commands and Tips

Calculate distinct count total at a group level

Re: Calculate distinct count total at a group level

Re: Calculate distinct count total at a group level