cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sunilbeckham
Frequent Visitor

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

My "Student" table looks something like this:

Student NumberSchool YearGradeGender
101202010M
102202011F
103202112M

 

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.
Thanks in advance.
1 ACCEPTED SOLUTION
olgad
Power Participant
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')))

View solution in original post

5 REPLIES 5
alxdean
Advocate II
Advocate II

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. 

bolfri
Solution Sage
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])
 
bolfri_0-1673996270814.png

 

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

 

olgad
Power Participant
Power Participant

3 is the total count of your students, and then from the formula i pasted you get this

olgad_0-1673995136185.png

Adjust the decimals on your measure so you see not 0, but 0.67 or 0.33

 

olgad
Power Participant
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')))

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 YearMeasure
20202
20211

 

But I'm getting this instead:

 

School YearMeasure
20203
20213

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.