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.
Hi guys,
Wondering if you can assist. I have a table with a column called Result.
Within the Result column, there are values such as:
Satisfactory
Did Not Attend
Poor
Excellent
I would like to create a measure that allows me to see the % of those who achieved 'Satisfactory' / over everyon who sat the exam. As there are values of Did not Attend, I will need to filter that out when dividing.
SUM 'RESULT' WITH VALUE = SATISFACTORY / SUM ' RESULT' SATISFACTORY & POOR & EXCELLENT.
How can i do this, because I keep coming up with errors. I have tried SUM and COUNT but cannot get my head around the filtering part.
Any guidance would be much apprecaited.
Solved! Go to Solution.
@Anonymous -
The following divides the selected result by all of the results, except for "Did Not Attend":
% Selected Result = DIVIDE( COUNTROWS(Exam), CALCULATE( COUNTROWS(Exam), Exam[Result] <> "Did Not Attend" ) )
The following divides "Satisfactory" in particular by everything except "Did Not Attend":
% Selected Result = DIVIDE( CALCULATE( COUNTROWS(Exam), Exam[Result] = "Satisfactory" ), CALCULATE( COUNTROWS(Exam), Exam[Result] <> "Did Not Attend" ) )
The second parameter of "CALCULATE" changes the "filter context".
Hope this helps,
Nathan
@Anonymous create a measure
Proud to be a Super User!
But we cannot divide by all, we need to filter out the do not attend.
@Anonymous -
The following divides the selected result by all of the results, except for "Did Not Attend":
% Selected Result = DIVIDE( COUNTROWS(Exam), CALCULATE( COUNTROWS(Exam), Exam[Result] <> "Did Not Attend" ) )
The following divides "Satisfactory" in particular by everything except "Did Not Attend":
% Selected Result = DIVIDE( CALCULATE( COUNTROWS(Exam), Exam[Result] = "Satisfactory" ), CALCULATE( COUNTROWS(Exam), Exam[Result] <> "Did Not Attend" ) )
The second parameter of "CALCULATE" changes the "filter context".
Hope this helps,
Nathan
Excellent, thanks a lot. This worked.
If i wanted to add another condition to the Does Not Contain, what is best practise?
Can I do as below?
& "Value"
@Anonymous -
You could do the following:
% Selected Result = DIVIDE( COUNTROWS(Exam), CALCULATE( COUNTROWS(Exam), NOT Exam[Result] IN {"Did Not Attend", "Satisfactory"} ) )
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |