Forum Discussion
Tracking student progress using Rank?
Hi Experts
I have the below data and I need to track the student progress based on assessments (assessment value), and we are tracking scores against 8 criteria (AS Quarter Code)at each stage.
Student ID Assess ID Assess Value ID Assessment Date Assessment Value Assessment Stage AS Quarter Code
020 100 1002 1 Jan 2024 89 Initial Q1
020 100 1003 1 Jan 2024 89 Initial Q7
020 100 1004 1 Jan 2024 70 Initial Q6
020 100 1005 1 Jan 2024 89 Initial Q2
020 100 1114 1 Jan 2024 95 Initial Q5
020 100 1116 1 Jan 2024 92 Initial Q4
020 100 1006 1 Jan 2024 86 Initial Q3
020 100 1009 1 Jan 2024 78 Initial Q8
020 101 2003 3 Mar 2024 96 Second Q2
020 101 1009 3 Mar 2024 105 Second Q5
020 101 1021 3 Mar 2024 125 Second Q7
020 101 1089 3 Mar 2024 120 Second Q4
020 101 1775 3 Mar 2024 115 Second Q3
020 101 2889 3 Mar 2024 60 Second Q1
020 101 3226 3 Mar 2024 75 Second Q8
I need to find a way to track each students score to show progress over the various assessment stages, there are also new students that have not reached assessment stage yet and will not have any scores. I thought perhaps indexing or ranking each student and then also aggregating to show averages scores at each stage for each group, but I am struggling to find the best solution.
I need to be able to show student scores as visuals in a dashboard
Any advice greatly received
Thanks
Ok let me rethink and update
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!Try this
#### 1. **Load Data into Power BI**
- **Import your dataset** by clicking "Get Data" and choosing your source (e.g., Excel or CSV).
- **Ensure the data types** are correctly set for each field, especially `Assessment Date` as Date and `Assessment Value` as Number.
#### 2. **Create Measures for Score Calculation**
- **Average Score per Stage**: This helps to calculate and display the average score for each assessment stage.
```DAX
AverageScore = AVERAGE('Table'[Assessment Value])
```
- **Score Comparison (Improvement or Decline)**: You can create a measure to show the difference between assessment stages (e.g., from "Initial" to "Review") for each student.
```DAX
ScoreDifference =
CALCULATE(
SUM('Table'[Assessment Value]),
'Table'[Assessment Stage] = "Review"
)
-
CALCULATE(
SUM('Table'[Assessment Value]),
'Table'[Assessment Stage] = "Initial"
)
```
#### 3. **Create Ranking for Students by Assessment Scores**
Ranking helps to understand student performance relative to others within the same stage.
```DAX
Rank =
RANKX(
FILTER(
'Table',
'Table'[Assessment Stage] = EARLIER('Table'[Assessment Stage])
),
'Table'[Assessment Value],
,
DESC,
DENSE
)
```
#### 4. **Create Visuals in Power BI**
- **Line Chart for Score Trend (Average Scores per Stage)**:
- **X-Axis**: `Assessment Stagee
- **Y-Axis**: `Average Score measure
- This chart will help visualize the change in average scores across assessment stages.
- **Table for Individual Student Progress**:
- Include `Student ID`, `Assessment Stage`, `Assessment Value`, and `Rank`.
- You can also add the **ScoreDifference** measure here to directly show progress or decline for each student.
#### 5. **Add Conditional Formatting for Score Progress**
- To highlight improvements or declines, you can apply conditional formatting to the `ScoreDifference` column (e.g., green for improvement and red for decline).
#### 6. **Slicer for Filtering by Student or Quarter Code**
- Add slicers for `Student ID` or `AS Quarter Code` to allow interactive filtering so you can track individual student progress or focus on specific assessment criteria.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
7 Replies
Steps to Track Student Progress and Visualize Scores in Power BI
Load Data into Power BI:
Open Power BI Desktop.
Click on “Get Data” and select your data source (e.g., Excel, CSV).
Load your data into Power BI.
Data Preparation:
Ensure your data types are correct (e.g., Assessment Date as Date, Assessment Value as Number).
Create Measures for Average Scores:
Go to the “Modeling” tab and create a new measure for the average score by assessment stage:
AverageScore = AVERAGE('Table'[Assessment Value])
Create a Rank Measure:
Create a measure to rank students based on their scores within each assessment stage:
Rank =
RANKX(
FILTER(
'Table',
'Table'[Assessment Stage] = EARLIER('Table'[Assessment Stage])
),
'Table'[Assessment Value],
,
DESC,
DENSE
)
Create Visuals:
Line Chart for Average Scores:
Add a line chart to your report.
Drag Assessment Stage to the X-axis.
Drag the AverageScore measure to the Y-axis.
Table for Detailed Scores and Ranks:
Add a table visual.
Drag Student ID, Assessment Stage, Assessment Value, and the Rank measure to the table.
Format and Customize:
Customize the visuals with titles, labels, and colors to make them more informative and visually appealing.
Example DAX Code
Here’s a summary of the DAX code you might use:
-- Measure for Average Score
AverageScore = AVERAGE('Table'[Assessment Value])
-- Measure for Ranking Students
Rank =
RANKX(
FILTER(
'Table',
'Table'[Assessment Stage] = EARLIER('Table'[Assessment Stage])
),
'Table'[Assessment Value],
,
DESC,
DENSE
)
Visuals in Power BI
Line Chart: This will show the trend of average scores across different assessment stages.
Table: This will display individual student scores along with their ranks within each assessment stage.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!Thank you saud968 , this has helped me to rank the student scores for each Q , but what I am really looking for is a way to compare the assessment scores at different stages (Inital and review). I dont so much need the averages but to show if the scores have improved or declined at each stage.
any ideas?
thanks again
Ok let me rethink and update
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
I am just adding the data again as it is not clear
Student ID Assess ID Assess Value ID Assessment Date Assessment Value Assessment Stage AS Quarter Code 1 235 5896 01-Jan 1 Initial Q1 1 235 5897 01-Jan 1 Initial Q2 1 235 5899 01-Jan 2 Initial Q3 1 235 5886 01-Jan 5 Initial Q4 1 235 5875 01-Jan 3 Initial Q5 1 235 5888 01-Jan 5 Initial Q6 1 235 5855 01-Jan 3 Initial Q7 1 235 5844 01-Jan 4 Initial Q8 1 335 4896 01-Mar 2 Review Q1 1 335 4897 01-Mar 2 Review Q2 1 335 4899 01-Mar 3 Review Q3 1 335 4886 01-Mar 6 Review Q4 1 335 4875 01-Mar 4 Review Q5 1 335 4888 01-Mar 6 Review Q6 1 335 4855 01-Mar 4 Review Q7 1 335 4844 01-Mar 5 Review Q8
