📊 Complete Database Schema for School Management Dashboard

Below is the complete relational schema for a school management dashboard, designed for use in Excel and Power BI. These tables are normalized and connected through keys to support DAX measures and visualizations effectively.

🏙️ 1. Cities Table

• CityID – Integer (Primary Key)
• CityName – Text
• State – Text
• Country – Text

🏫 2. Schools Table

• SchoolID – Integer (Primary Key)
• SchoolName – Text
• CityID – Integer (Foreign Key → Cities)
• EstablishedYear – Integer
• Type – Text (e.g., Public / Private)

👩‍🏫 3. Teachers Table

• TeacherID – Integer (Primary Key)
• FullName – Text
• Gender – Text (M / F)
• SchoolID – Integer (Foreign Key → Schools)
• Subject – Text
• JoiningDate – Date
• Salary – Decimal

🎓 4. Students Table

• StudentID – Integer (Primary Key)
• FullName – Text
• Gender – Text (M / F)
• DOB – Date
• SchoolID – Integer (Foreign Key → Schools)
• Class – Text (e.g., 5th, 10th)
• EnrollmentYear – Integer

💸 5. Fees Table

• FeeID – Integer (Primary Key)
• StudentID – Integer (Foreign Key → Students)
• Month – Text (e.g., Jan 2025)
• Amount – Decimal
• PaidOn – Date
• PaymentMode – Text (Cash / Online / Card)

💼 6. Salaries Table

• SalaryID – Integer (Primary Key)
• TeacherID – Integer (Foreign Key → Teachers)
• Month – Text (e.g., Jan 2025)
• Amount – Decimal
• PaidOn – Date
• PaymentMode – Text (Bank / Cash / Cheque)

🔗 Relationship Summary

Cities
  ↑
  |
Schools
  ↑            ↑
  |            |
Teachers     Students
  ↑            ↑
  |            |
Salaries      Fees
  

Each table uses IDs as primary and foreign keys to maintain relational integrity and optimize DAX-based queries in Power BI. These relationships allow you to filter students by city, calculate total fees by school, or average teacher salary by subject.

📈 DAX Measures for School Management KPIs in Power BI

After building your data model and connecting tables using relationships, the next step is to create DAX measures that offer powerful insights into your school system. Below are common and advanced KPIs you can calculate using simple DAX formulas.

✅ Total Students

TotalStudents = COUNTROWS(Students)

This measure counts all students across all schools.

✅ Total Fees Collected

TotalFees = SUM(Fees[Amount])

Calculates total fees collected from all students.

✅ Average Teacher Salary

AverageSalary = AVERAGE(Salaries[Amount])

This shows the average salary paid to teachers.

✅ Students by City

StudentsByCity = 
    COUNTROWS(Students)
  

Use a matrix visual and place CityName from the Cities table on rows with this measure in values.

✅ Teachers by City

TeachersByCity = 
    COUNTROWS(Teachers)
  

Create a matrix with CityName from Cities and this measure to find teacher distribution.

✅ Teachers by School

TeachersBySchool = 
    COUNTROWS(Teachers)
  

Use a matrix with SchoolName from Schools and this measure.

✅ Fees Per Student

FeesPerStudent = 
    CALCULATE(
        SUM(Fees[Amount]),
        ALLEXCEPT(Fees, Fees[StudentID])
    )
  

Returns the total fee paid by each student. Use it in a table with StudentID or FullName.

✅ Fees Per School

FeesPerSchool = 
    CALCULATE(
        SUM(Fees[Amount]),
        ALLEXCEPT(Schools, Schools[SchoolID])
    )
  

Use a matrix or bar chart with SchoolName and this measure to compare fee collections across schools.

✅ Fees Per City

FeesPerCity = 
    CALCULATE(
        SUM(Fees[Amount]),
        ALLEXCEPT(Cities, Cities[CityID])
    )
  

Compare total fee revenue across cities.

✅ Total Students Per School

StudentsPerSchool = 
    CALCULATE(
        COUNTROWS(Students),
        ALLEXCEPT(Schools, Schools[SchoolID])
    )
  

Displays number of students in each school. Great for capacity planning visuals.

✅ Monthly Fee Trend

MonthlyFeeTrend = 
    SUM(Fees[Amount])
  

Use this measure with the Month column in a line chart to see monthly collection trends.

✅ Average Fee Per Student

AverageFeePerStudent = 
    DIVIDE(SUM(Fees[Amount]), DISTINCTCOUNT(Fees[StudentID]))
  

This gives you how much on average a student is paying.

✅ Average Salary Per City

AverageSalaryPerCity = 
    AVERAGEX(
        VALUES(Schools[CityID]),
        CALCULATE(AVERAGE(Salaries[Amount]))
    )
  

Calculates average salary distributed to teachers grouped by city.

You can use these DAX measures inside various visuals like bar charts, cards, matrices, and slicers. Combining them allows you to build a dynamic, interactive Power BI dashboard that serves school admins, HR, and accountants alike.