cancel
Showing results for
Did you mean:
Frequent Visitor

## Dax help in filtering numerator but keeping denominator fixed for the same column

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:

Measure = DIVIDE(COUNTA('Student'[Student Number]),CALCULATE(COUNTA('Student'[Student Number]),ALL('Student'[Student Number])))

I'm getting 1 as the answer for all my calculations since the filter is also being applied to the denominator. Please advice what can I change in this formula to make it work.
1 ACCEPTED SOLUTION
Power Participant
Hi there,
you have to remove the filters coming from any column of the Student table.
DIVIDE
(COUNTA('Student'[Student Number]),CALCULATE(COUNTA('Student'[Student Number]),ALL('Student')))
5 REPLIES 5

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.

Solution Sage
Number of students = COUNTA(Student[Student Number])
Total Students = CALCULATE([Number of students],ALL(Student))
Share of Total = DIVIDE([Number of students],[Total Students])

``````Share of Total Combined =
var students = COUNTA(Student[Student Number])
var totalstudents = CALCULATE(COUNTA(Student[Student Number]),ALL(Student))
return DIVIDE(students,totalstudents)``````

Power Participant

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

Power Participant
Hi there,
you have to remove the filters coming from any column of the Student table.
DIVIDE
(COUNTA('Student'[Student Number]),CALCULATE(COUNTA('Student'[Student Number]),ALL('Student')))
Frequent Visitor

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

 School Year Measure 2020 3 2021 3

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!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors