Compare individual student performance to the entire population
This is for all my fellow teacher friends out there. It's the end of the year and I know you're busy marking final exams and preparing report cards. Some of you may even have the good fortune to be done already. For those of you who are still hard at work, I'm going to show you how to use Power BI to compare the results of one student to the entire distribution of your class.
The Problem
This problem was presented to me by @Dhuey on the Power BI Community forums.
How can we easily compare one student's performance with the entire class? In this example, @Dhuey wanted to highlight the result of the selected student, but leave all other students' results visible, as you can see in the below image.
As you can see in the image above, Matrix 2, when we select an individual student, their results appear, but all other students' results are filtered out. This the expected behavior of slicer in Power BI. How then do we achieve the result of Matrix 1, with highlighting and keeping the results of all students?
This can be easily done in Power BI using a bit of DAX, conditional formatting, and a duplicate table.
The Solution
Initial Setup: Get Data
You can follow along by pasting the code below into a New Blank Query in Power BI desktop:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZS7csIwEEV/JeM2eMaLX1KJ1KQPHUOXtDTA/2MJyTpeQdLYdzXrY+3VtU+n5tp10uya79v95/dy+wj60C6Xo/+q9FPOy819Zn3eRcYejKBdei5on5qnsP6UBssmM3ow+tLb433UhriEGIAYFOI1IQ+4IkYgRjTv0TwVNwxlQkxATEAMSqfnLMg2M2Yw5rJ97ogaLeuZGCBouCidz2rG+gqxgFg0BB89LF1tpL2JIR0T1pXJRUkMc1D7kE1Kpby9moaQVlOY01i8MnNUe6nSLoyqIF06JJusVrYwrYJA6MT/ETVhXGORT2KELzopXs/DxMbC4bvfaFIqWxhaQSR72PLfQExtLHDODllBaHXwhaGNhcMH2NbabnT+pTG1sXhD8QWSLVkg5wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student Code" = _t, #"Student Name" = _t, ENG_OG = _t, ENG_Tch = _t, HSS_OG = _t, HSS_Tch = _t, MAT_OG = _t, MAT_Tch = _t, SCI_OG = _t, SCI_Tch = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Code", type text}, {"Student Name", type text}, {"ENG_OG", type text}, {"ENG_Tch", type text}, {"HSS_OG", type text}, {"HSS_Tch", type text}, {"MAT_OG", type text}, {"MAT_Tch", type text}, {"SCI_OG", type text}, {"SCI_Tch", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Student Code", "Student Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "Subject"}, {"OG", "Result"}})
in
#"Renamed Columns"
This code will create a table of student results. I have kept it in the format the @Dhuey provided, so there are some nifty Power Query tricks in here using unpivot and pivot to tidy up the data into a report friendly format. All of this can be done with the click of a few buttons!
Rename this Query StudentData.
Duplicate the StudentData query and call the second one StudentDataAll. (Right click the StudentData query in the Queries pane and select Duplicate).
Click Enter Data and paste the table below into Power BI. Call this DimResults.
| Order | Result |
|---|---|
| 1 | A+ |
| 2 | A |
| 3 | A- |
| 4 | B+ |
| 5 | B |
| 6 | B- |
| 7 | C+ |
| 8 | C |
| 9 | C- |
| 10 | D+ |
| 11 | D |
| 12 | D- |
| 14 | F |
| 15 | NA |
Pro tip: Create new tables and columns in Power Query (not DAX) to keep your slicers and report page loads more efficient.
Close and Apply the changes to your Power Query.
Create Relationships
Now that we have all the tables loaded, we can address the all important concept of relationships in Power BI. In order to get the table to highlight but not filter when we select the slicer, we need to ensure there is no relationship between Student Code to Student Code, and that there is a relationship on Results. If you have named everything properly, Power BI should create the relationships for you:
Create the Report
Now we can create the basic report. Build a report page with a slicer and a matrix.
Slicer:
Put StudentData[StudentName] in a slicer visual.
Matrix:
Create a matrix with:
- DimResults[Result] in Columns
- StudentDataAll[Subject] in Rows
- StudentDataAll[Result] in Values > set the aggregation to COUNT
Note that we are using the StudentData table for the Slicer, but the StudentDataAll for the matrix. This means that the matrix won't update when we select a student in the slicer.
Final Solution: The DAX
Ok, now for the cool part. How do we get the slicer to highlight based on the slicer selection? We'll use a bit of DAX and conditional formatting.
Create a new MEASURE:
Count Result =
IF (HASONEVALUE ( 'StudentData'[Student Name] ),
COUNTROWS (
FILTER (
'StudentDataAll',
'StudentDataAll'[Student Code] = SELECTEDVALUE ( 'StudentData'[Student Code] )
)
),
0
)
What is this formula actually doing?
HASONEVALUE() is checking if there is one student name in the filter/slicer selection. This ensures the table won't highlight everything when no students are selected.
COUNTROWS is counting the total results, and we have chosen to FILTER the StudentDataAll table as our table to count. You may recall that the StudentDataAll table is not connected to the StudentData table by Student Code, so when we filter for student, nothing happens.
FILTER is checking to see what student has been selected, and only returns results that match the selected student. SELECTEDVALUE checks for the single value of student that has been selected by the slicer. Note this uses the StudentData table, as that is the table we put in our slicer above.
Cool, so this creates a measure which updates based on the slicer selection, but the matrix still does nothing.
That is pretty nifty, but now we need to apply the conditional formatting.
Conditional Formatting
The last step, click the down arrow next to Count of Result in your matrix, and choose Conditional Formatting > Background Color.
Use the new [Count Result] measure we just created to apply conditional formatting to the matrix.
Click OK.
Test it out. Download the completed Student Reports file to have a play.
