cancel
Showing results for
Did you mean:
Frequent Visitor

Calculate distinct count total at a group level

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?

1 ACCEPTED SOLUTION
Super User
``````# 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])``````

2 REPLIES 2
Super User
``````# 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])``````

Frequent Visitor

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

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors