2022: Week 5 - Solution
Solution by Tom Prowse and you can download the workflow here.
This week's challenge is looking to take the numeric score our students' have received and turn it to:
- A letter grade (our students' parents prefer this)
- A score that goes towards their High School applications
The challenge's aim is understand how many points on average a student who receives an A gets.
Step 1 - Pivot Subjects
Step 2 - Distributed Grade Scores
The next step is to distribute our grades based on the subject and score. These need to be in evenly distributed groups where each number of students gain the same grade within a subject.
For this we are going to use the Tiles functionality in Tableau Prep. This will allow us to partition our data and then return a value that has been split into 6 percentiles.
You can find the Tiles feature by pressing on the three dots, then going to calculated field, then choosing Tile. The setup for the Tiles feature is similar to an LOD or Rank, as it uses the visual analytical calculation editor, and has the following setup:
This has now split each of the students and subjects into 6 equal groups based on their score. The highest scores go into 1 and lowest into 6.
Our table should now look like this:
Step 3 - Grades & Points
First we want to duplicate the Grade field, and then we can rename each of the grade numbers by double clicking on the value within the profile pane and renaming them to the assign grades in the requirements. 1 is the highest (grade A) - 6 is the lowest (grade F).
We can do the same for points (this is why we duplicated the field), and rename each of the groups based on tile 1 being the highest (10 points) and then reducing by 2 for each of the tiles until we get to 6 which is the lowest (1 point).
Our table should now look like this:
Step 4 - Average Points
Next we want to calculate how many points each student has earned across their different subjects. To do this we want to use an LOD calculation so that we can maintain the row level detail but also look across multiple rows.
Total Points per Student
Next we need to calculate the average total points per student per grade. Again we need a Fixed LOD here, but this time we want to group by Grade and then calculate the Avg Total Points per Student:
Avg student total points per grade
Then finally we can round this average to 2 decimal places:
Avg student total points per grade
ROUND([Avg student total points per grade],2)
After calculating the average total points scores, our table should look like this:
Step 5 - Remove A Grades
The final task is to answer the question, from the requirements, 'How many students scored more than the average if you ignore their As?'
To do this we want to exclude any A grades from our table, we can do this by selecting A in the Grade field within the profile pane and then pressing 'Exclude'.
Then finally we want to filter the table so that we remove any Students how had a Total Points score of greater than 41.15 (Grade A average points) - [Total Points per Student]>41.15
After filtering the data we are left with a table that is ready for our Output:
