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!
