Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
# 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])
# 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])
Wow. Thanks, Daniel. Your measures are working as expected.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |