Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sunilbeckham
Helper I
Helper I

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
Super User
Super User

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')))

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

5 REPLIES 5
alxdean
Advocate V
Advocate V

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
Super User
Super User

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)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




olgad
Super User
Super User

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

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
olgad
Super User
Super User

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')))

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.