| OLAG SHS ENTRANCE EXAM — POWER BI KPI & GUIDE REFERENCE |
|
|
|
|
| SECTION A — KPI CATALOGUE (30 KPIs) |
| KPI Name |
DAX / Definition |
Visual Type |
Page |
| ── Volume & Registration ── |
| Total Applicants |
Count the total number of rows in the Students table. Each row represents one applicant. |
Card |
Summary |
| Applicants by Year |
Count students grouped by the ExamYear column. Use this in a chart axis to show volume per year. |
Line / Column |
Trends |
| Applicants by Region |
Count students grouped by the Region column. Place Region on the axis of a bar chart or as the location field of a filled map. |
Filled Map + Bar |
Regional |
| Applicants by Program |
Count students grouped by the Program column. Works well as a donut or clustered bar chart. |
Donut / Bar |
Programs |
| Applicants by Exam Center |
Count students grouped by ExamCenterID, then relate to ExamCenters[CenterName] for readable labels. |
Bar / Map |
Centers |
| Applicants by Gender |
Count students grouped by the Gender column. Compare Male vs Female counts as a pie chart or side-by-side cards. |
Pie / Card |
Summary |
| Unique JHS Feeders |
Count the number of distinct schools in the JHSAttended column — tells you how many unique schools sent applicants. |
Card |
Schools |
| Top 10 Feeder JHS |
Group students by JHSAttended, count per school, then apply a Top N filter (N=10) to show only the highest-sending schools. |
Bar Chart |
Schools |
| Yearly Growth Rate |
Subtract last year’s applicant count from this year’s, then divide by last year’s count. Multiply by 100 for a percentage. Use a KPI visual to show direction. |
KPI / Card |
Trends |
| ── Score KPIs ── |
| Avg English Score |
Calculate the arithmetic mean of the EnglishScore column in the ExamResults table. Display on a gauge or card, benchmarked against 60 (the midpoint). |
Card / Gauge |
Scores |
| Avg Maths Score |
Calculate the arithmetic mean of the MathsScore column. Compare against Avg English and Avg Science on a clustered bar for subject comparison. |
Card / Gauge |
Scores |
| Avg Science Score |
Calculate the arithmetic mean of the ScienceScore column. Use alongside English and Maths averages to spot the weakest subject across cohorts. |
Card / Gauge |
Scores |
| Avg Total Score |
Calculate the arithmetic mean of the TotalScore column. This is the primary performance headline — display prominently on the summary page. |
Card |
Scores |
| Highest Total Score |
Find the maximum value in the TotalScore column. Use a card visual to highlight the top-performing applicant each year. |
Card |
Scores |
| Lowest Total Score |
Find the minimum value in the TotalScore column. Useful for understanding the floor of performance and identifying at-risk applicants. |
Card |
Scores |
| Score Std Deviation |
Measure how spread out scores are around the average. A high value means scores vary widely; a low value means most students scored similarly. Best shown as a histogram. |
Histogram |
Scores |
| Score Percentile Band |
Group TotalScore into defined ranges (e.g. Below 150 / 150–179 / 180–209 / 210–239 / 240+) using a calculated column. Count applicants in each band for a distribution bar chart. |
Clustered Bar |
Scores |
| Subject with Lowest Avg |
Compare the three subject averages (English, Maths, Science) and identify which one is lowest. Display as a single card with conditional formatting to flag the weakest area. |
Card (conditional) |
Scores |
| ── Admission KPIs ── |
| Total Admitted |
Count rows in ExamResults where the AdmissionStatus column equals ‘Admitted’. |
Card |
Summary |
| Total Not Admitted |
Count rows in ExamResults where the AdmissionStatus column equals ‘Not Admitted’. |
Card |
Summary |
| Overall Admission Rate |
Divide Total Admitted by Total Applicants. Format as a percentage. Show on a gauge visual with target line at 70%. |
Card / Gauge |
Summary |
| Admission Rate by Program |
For each programme, divide its admitted count by its total applicant count. Use a clustered bar chart to compare all five programmes side by side. |
Clustered Bar |
Programs |
| Admission Rate by Region |
For each region, divide its admitted count by its total applicant count. Place on a filled map to see geographic performance patterns. |
Filled Map |
Regional |
| Admission Rate by Center |
For each exam centre, divide its admitted count by its total applicant count. Helps identify centres that consistently produce high-performing applicants. |
Bar / Table |
Centers |
| Admission Rate by Year |
For each exam year, divide admitted count by total applicant count. Plot as a line chart to reveal improvement or decline trends over time. |
Line Chart |
Trends |
| Admission Rate by Gender |
For each gender group, divide admitted count by total applicant count. Reveals any gender-based performance gap. |
Clustered Bar |
Summary |
| Above Threshold Flag |
Create a calculated column that checks whether a student’s TotalScore is greater than or equal to their chosen programme’s threshold. Returns 1 (Yes) or 0 (No). Use as a filter or in a matrix. |
Table / Filter |
Scores |
| Program Competitiveness Index |
Divide each programme’s admission threshold by 300 (maximum score) to express it as a percentage. Higher percentage = more competitive programme. Display as a sorted bar chart. |
Bar Chart |
Programs |
| Centers Above National Avg |
First calculate the national average total score. Then count how many exam centres have a centre-level average that exceeds it. Show as a card alongside a table listing those centres. |
Card + Table |
Centers |
|
|
|
|
| SECTION B — DATA MODEL RELATIONSHIPS |
| From Table : Column |
To Table : Column |
Cardinality |
Filter Direction |
| Students : ApplicantID |
ExamResults : ApplicantID |
1 : 1 (one-to-one) |
Both |
| Students : ExamCenterID |
ExamCenters : CenterID |
Many : 1 |
Single (→ Centers) |
| Students : Program |
Programs : ProgramName |
Many : 1 |
Single (→ Programs) |
|
|
|
|
| SECTION C — SUGGESTED DASHBOARD PAGES |
| Page Name |
Suggested Visuals |
Key Slicer(s) |
Story / Question Answered |
| Executive Summary |
Admission Rate card, Total Applicants card, Avg Score card, Gender pie, Yearly trend line |
ExamYear |
How are we performing overall? |
| Regional Analysis |
Filled map + bar of applicants & pass rates by region, top region card |
ExamYear, Program |
Which regions send most/best students? |
| Program Analysis |
Score by program clustered bar, admission rate by program, program competitiveness index |
ExamYear, Region |
Which programs are most competitive? |
| Center Performance |
Applicants per center bar, admission rate per center, map of centers |
ExamYear, Region |
How well does each exam center perform? |
| Score Deep-Dive |
Score histograms, subject avg comparison, score band distribution, scatter Eng vs Maths |
ExamYear, Program |
Where are the score strengths/weaknesses? |
| Feeder Schools |
Top 20 feeder JHS bar, region filter, table with admission rate per school |
Region, Program |
Which JHS schools feed us most applicants? |
| Trends Over Time |
Yearly applicants line, admission rate trend, score trend, growth rate KPI |
Program, Region |
Are we growing and improving year on year? |
