Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
My "Student" table looks something like this:
Student Number | School Year | Grade | Gender |
101 | 2020 | 10 | M |
102 | 2020 | 11 | F |
103 | 2021 | 12 | M |
I'm trying to create a measure to calculate the percentage of filtered numerator vs fixed denominator. For example 1/3 (33%) Where the filters on the numerator are selected by the user could be 'Grade','Gender' and '1' is the COUNTA(Student Number). While the denominator is not affected by the filter and only gives '3' as COUNTA(Student Number).
Currently my calculation looks like this:
Solved! Go to Solution.
your ALL('Student') will remove ALL filters on the student table and always return all records. I.2. 2 + 1=3. if you want to count all per category, you need to be more precise about your ALL statement. for example
ALl(Student[Gender]) will count all Genders, but keep other filters active, such as Year.
Share of Total Combined =
var students = COUNTA(Student[Student Number])
var totalstudents = CALCULATE(COUNTA(Student[Student Number]),ALL(Student))
return DIVIDE(students,totalstudents)
Proud to be a Super User!
3 is the total count of your students, and then from the formula i pasted you get this
Adjust the decimals on your measure so you see not 0, but 0.67 or 0.33
Thank you for your quick response. I have tried that approach as well, but I just get 0 as a result of this calculation. If I isolate just this part, then I get incorrect values of student numbers if I select any other column along with it in my table.
CALCULATE(COUNTA('Student'[Student Number]),ALL('Student'))
For example, using this calculation if I select the column School Year, and Measure, then ideally I should get this:
School Year | Measure |
2020 | 2 |
2021 | 1 |
But I'm getting this instead:
School Year | Measure |
2020 | 3 |
2021 | 3 |