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
power-bi-noob
Helper I
Helper I

Summary of results from a table

We have a cybersecurity course that we require all student employees to take when they are hired. Their managers are supposed to verify they have completed this, but usually it is a low priority for them. We'd like to give the managers a reminder if a student does not complete the course or fails it.

 

The students must complete all of the assessments and pass the course with an 80%. The exam they take is weighted double of each assignment. There is also a pre-course test they take to measure their current knowledge of the subject. The pre-course test is required, but not calculated into the final grade. If they get less than 80%, they need to retake the course.

 

The table I have shows their results like this:

name, assessment, score

employee1, pretest, 80

employee1, assignment1, 80

employee1, assignment2, 80

employee1, exam, 160

employee2, pretest, 100

employee2, assignment1, 80

employee2, assignment2, 80

employee3, pretest, 50

employee3, assignment1, 80

employee3, assignment2, 80

employee3, exam, 140

 

In the example above, employee1 has an 80 on all assessments and has passed the course. employee2 has an 80% on 3 of the assessments, but did not take the exam. employee3 completed all assessments, but failed to get an 80%.

 

What I'd like to get is something like this:

name, score, status

employee1, 80%, passed

employee2, 80%, incomplete

employee3, 75%, failed

 

I know the formula for calculating the final grade is simply (assignment1+assignment2+exam)/400, and the pass/fail would be final grade >= 80 or final grade < 80. Not sure how to get started on the summarized results though. Any guidance would be appreciated!

4 REPLIES 4
helpme
Resolver I
Resolver I

Hi,

 

I would start with using the advanced editor in power query to set out the conditions for passing where PREVIOUSSTEP is your previous step:

 

 

 

 

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Pretest taken", each if [assessment] = "pretest" and [score] <> null then "Y" else "N"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Assignment passed", each if [assessment] = "assignment1" and [score] >= 80 then "Y" else "N"),
    #"Added Custom2" = Table.AddColumn(PREVIOUSSTEP, "Exam taken", each if [assessment] = "exam" then "Y" else "N"),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"name"}, {{"Pretest", each List.Max([Pretest taken]), type text}, {"Assignment", each List.Max([Assignment passed]), type text}, {"Exam", each List.Max([Exam taken]), type text}, {"Score", each _, type table [name=nullable text, assessment=nullable text, score=nullable number, Pretest taken=text, Assignment passed=text, Exam taken=text]}}),
    #"Expanded Score" = Table.ExpandTableColumn(#"Grouped Rows", "Score", {"assessment", "score"}, {"assessment", "score.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Score", each ([assessment] <> "pretest"))
in
    #"Filtered Rows1"

 

 

 

 

Next, I would create a column in DAX to calculate the final score and change the type to percentage afterwards.

 

 

 

Score = 
var overallscore = CALCULATE( sum ('Table'[score.1]), FILTER('Table', [name] = EARLIER([name])))
return
 divide (overallscore,400, blank())

 

 

 

 

then, I would create another DAX column to find the state:

 

 

 

 

State = if([Score] >= 0.8 && [Exam] = "Y", "Passed", "Failed")

 

 

 

 

Not sure yet how to do "incomplete" status! but this is halfway there:

 

incomplete = if ( [Exam] = "N", SWITCH([State], "Failed", "Incomplete"))
amitchandak
Super User
Super User

@power-bi-noob , Try a measure like

 

Divide( countrows(filter(Table, Table[score] > 80)),countrows(Table))

Hi @amitchandak , I think that is taking me closer.

grade summary = DIVIDE(COUNTROWS(FILTER(submission_dim,submission_dim[grade]>80)),countrows(submission_dim))

First run gets me this error:

powerbinoob_0-1665083998799.png

 

The grades are stored as text, not integers.

Then I tried this.

 

grade summary = DIVIDE(COUNTROWS(FILTER(submission_dim,value(submission_dim[grade])>80)),countrows(submission_dim))

 

and got this error:

powerbinoob_1-1665084017451.png

 

I modified it further to try and use a conditional , but then the last argument shows up as an an unexpected value.

 

grade summary = DIVIDE(COUNTROWS(FILTER(submission_dim,
if(submission_dim[grade] <> "\N", value(submission_dim[grade])>80, 0))
,countrows(submission_dim))

 

Not sure where I'm going wrong here, all of my parenthesis appear to be balanced.

@power-bi-noob , oh Grade is text ?

Try like

grade summary = DIVIDE(COUNTROWS(FILTER(submission_dim,
if(submission_dim[grade] <> "\N", value(submission_dim[grade])>"80", 0))
,countrows(submission_dim))

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.