Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"} ) )
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |