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.
Afternoon everyone,
I have a load of tiered measures and have run into a problem. I use this to calculate the number of students below 90%
Persistent Absence = VAR __ISINSCOPE = ISINSCOPE ( Attendance[Year] ) VAR __PACore = CALCULATE(DISTINCTCOUNT(Attendance[UPN-SUID]),FILTER( VALUES('Attendance'[UPN-SUID]), [Attendance Percentage YTD] <= 0.90)) RETURN IF ( __ISINSCOPE, __PACore, CALCULATE(__PACore,NOT('Attendance'[Year] IN {"Nursery", "Reception"} )))
I then use the following to find the percentage:
Persistent Absence % = Divide([Persistent Absence YTD], DISTINCTCOUNT(Attendance[UPN-SUID]))
I then have multiple measures that give me breakdowns of the percentage such as:
Male Persistent Absence % YTD = Calculate([Persistent Absence % YTD], 'Student Database (New)'[Gender] IN {"Male"})
The problem is that I only want to filter the numerator of the second measure by the 3rd and not the denominator. Is there a way to make:
Persistent Absence % = Divide([Persistent Absence YTD], DISTINCTCOUNT(Attendance[UPN-SUID]))
the element in bold not filterable by the 'Student Database (New)' table?
The alternative is editing multiple measures rather than just one.
Thanks,
Stan
Hey,
not if I understand your question correctly, but why don't you just rewrite this
Persistent Absence % = Divide([Persistent Absence YTD], DISTINCTCOUNT(Attendance[UPN-SUID]))
to this
Persistent Absence % = Divide([Persistent Absence YTD], CALCULATE(DISTINCTCOUNT(Attendance[UPN-SUID]) , ALL('Student Database (New)')))
Regards,
Tom
Hi Tom,
Sorry if that was less than clear.
Say I have 1000 kids in a school and 100 are below 90%.
The second calculation would be 100/1000 and = 10%
When I add a filter on the third measure it changes everything so for males say I had 50 below 90% and had 500 males in the school the third measure currently returns:
50/500 when it should return 50/1000 which is the total school population rather than the subgroup total.
Using the measure you suggested seems to make everything way off.
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 |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |