Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

Student Performance Project

In this project, you will create an interactive Power BI report to analyze student academic performance. You will examine examination scores, attendance, subjects, class groups, and student-level results. The report will help educators identify performance patterns and understand where additional academic support may be needed.

Project Scenario

The academic team at CIIT TRANING INSTITUTE wants a report that summarizes student performance across subjects and classes. Teachers need to compare average marks, pass rates, attendance, and individual student results.

Your responsibility is to build a report that presents these indicators clearly while protecting student information and avoiding unsupported conclusions.

1. Project Objectives

  • Import student and examination data into Power BI.
  • Clean the dataset and validate marks and attendance values.
  • Build a suitable data model.
  • Create DAX measures for academic performance.
  • Visualize results by subject, class, and student.
  • Add slicers to make the report interactive.
  • Validate calculations and communicate findings responsibly.

Project Workflow

Student Data Import files Clean Data Power Query Calculate DAX measures Analyze Report findings

2. Prepare the Student Dataset

Begin with a spreadsheet containing student information and assessment results. The following sample structure can be used to plan your data. The example values are illustrative, not actual student records.

Column Example Purpose
StudentID STU001 Unique student identifier.
StudentName Samadhan Displays the student's name where authorized.
Class Class A Compares performance across classes.
Subject Mathematics Analyzes marks by subject.
ExamDate 2026-01-20 Supports examination-date analysis.
MarksObtained 78 Records the student's score.
MaximumMarks 100 Defines the maximum possible score.
AttendancePercent 92 Records attendance percentage.
Data design tip: If each student takes multiple subjects or examinations, store each student-assessment combination as a separate row. Do not put several subjects into one cell.

3. Import and Clean the Data

  1. Open Power BI Desktop.
  2. Select Home → Get data → Excel or choose the CSV connector.
  3. Select the appropriate worksheet or table.
  4. Choose Transform Data to open Power Query Editor.
  5. Remove unnecessary blank rows and review duplicate records.
  6. Check that StudentID is consistently formatted.
  7. Set ExamDate to Date and marks to Whole Number or Decimal Number as appropriate.
  8. Check for missing marks, invalid maximum marks, and attendance outside the expected range.
  9. Choose Close & Apply to load the cleaned data.

Data Validation Checks

Check before analysis Student identifiers are consistently formatted Marks are between zero and maximum marks Attendance values follow the agreed definition Missing values and duplicate assessments are reviewed
Important: Do not automatically replace missing marks with zero. A missing mark may mean an assessment was not completed, was not recorded, or does not apply. Confirm the meaning before deciding how to handle it.

4. Design the Data Model

For a small dataset, a single table may be sufficient. For a larger model, separate student, subject, class, and date information into dimension tables and keep assessment results in a central fact table.

Example Student Performance Model

FactResults StudentKey SubjectKey, DateKey Marks, MaximumMarks DimStudent Student, Class DimSubject Subject, Category DimDate Date, Month, Year DimClass Class, Section

Use matching keys to create relationships from the dimension tables to FactResults. The dimension-side keys should be unique. If Class is already stored in DimStudent, a separate DimClass table is optional and should be included only when the model design requires it.

5. Create DAX Measures

The following examples assume a table named FactResults with one row per student and assessment. Change the table and column names to match your model.

Total Marks Obtained

Total Marks Obtained = SUM(FactResults[MarksObtained])

Total Maximum Marks

Total Maximum Marks = SUM(FactResults[MaximumMarks])

Overall Percentage

Overall Percentage = DIVIDE( [Total Marks Obtained], [Total Maximum Marks], 0 )

Format this measure as a percentage. For example, a result of 0.78 should display as 78%.

Average Marks

Average Marks = AVERAGE(FactResults[MarksObtained])

Assessment Count

Assessment Count = COUNTROWS(FactResults)

Average Attendance

Average Attendance % = AVERAGE(FactResults[AttendancePercent]) / 100

This last measure assumes AttendancePercent is stored on a 0–100 scale. Format the measure as a percentage. If attendance is stored on a 0–1 scale, remove the division by 100.

Pass rate requires a defined rule. Before calculating it, confirm the passing threshold and whether passing is determined per subject, per examination, or across the complete academic record. Do not classify students using an arbitrary threshold.

6. Create a Student Performance Dashboard

Place the most important indicators at the top. Use the remaining space for subject comparisons, performance distributions, and student details.

Suggested Dashboard Layout

STUDENT PERFORMANCE REPORT Average Marks 78 Overall Percentage 78% Assessments 240 Attendance 92% Average Marks by Subject Maths Science English Marks Distribution Student Detail Table and Filters Student ID Class Subject Marks Attendance Result Use slicers to filter class, subject, and exam date

The values in this wireframe are illustrative. Replace them with results calculated from your own dataset.

7. Add Appropriate Visualizations

Visual Fields or measures Purpose
Card Average Marks Summarizes the average score.
Card Overall Percentage Shows marks obtained relative to maximum marks.
Card Assessment Count Shows the number of assessment records in the current context.
Bar chart Subject, Average Marks Compares performance across subjects.
Column chart Class, Average Marks Compares class-level averages.
Histogram-style chart Marks grouped into ranges Shows the distribution of marks.
Table or matrix Student, Subject, Marks, Attendance Supports detailed review by authorized users.
Slicer Class, Subject, ExamDate Allows users to filter the report.

Compare, Monitor, and Investigate

Compare Subjects Use bar charts Monitor Trends Use date-based visuals Review Details Student record Subject result Attendance Use tables or matrices

8. Add Slicers and Interactions

  1. Add a Class slicer.
  2. Add a Subject slicer.
  3. Add an ExamDate or academic-period slicer where appropriate.
  4. Test whether selecting a subject updates the relevant cards and charts.
  5. Use Format → Edit interactions to control which visuals respond to each selection.
  6. Test combinations of slicers and verify the resulting totals.
Report design tip: Keep filters easy to find. Use clear titles and consistent formatting, and avoid displaying unnecessary personal details on summary pages.

9. Protect Student Information

Student performance data is sensitive. Design the report so that users see only the information they are authorized to access.

  • Use sample or anonymized records while learning.
  • Display student names only when there is a legitimate need.
  • Restrict access to detailed student-level information.
  • Use appropriate workspace and report permissions when publishing.
  • Consider Row-Level Security when different users should see different records.
  • Follow the institution's data protection and retention requirements.
Important: Hiding a column or visual is not a security boundary. Apply and test appropriate access controls before sharing reports containing student-level information.

10. Validate the Results

  • Compare total marks with the source dataset.
  • Verify the percentage calculation against a manual example.
  • Confirm the number of records included in each visual.
  • Check how missing marks affect averages and percentages.
  • Ensure class and subject filters return the expected records.
  • Confirm that no student is accidentally counted multiple times in a student count.
  • Check titles, labels, formatting, and accessibility.

Example Calculation

Suppose a student obtains 78 marks out of a maximum of 100. The percentage is calculated as:

Percentage = Marks Obtained / Maximum Marks Percentage = 78 / 100 = 0.78 = 78%

If the same student has multiple subjects, calculate the overall percentage using total marks obtained divided by total maximum marks when that matches the institution's assessment rules.

11. Practical Assignment

Build Your Own Student Performance Report

  1. Prepare a student assessment dataset with StudentID, Class, Subject, MarksObtained, and MaximumMarks.
  2. Import and clean the dataset using Power Query.
  3. Create the required relationships if your model uses multiple tables.
  4. Create measures for Total Marks Obtained, Total Maximum Marks, Overall Percentage, Average Marks, and Assessment Count.
  5. Create KPI cards for key academic indicators.
  6. Add a subject comparison chart and a class comparison chart.
  7. Add Class, Subject, and ExamDate slicers.
  8. Validate the report against the source data.
  9. Write three evidence-based observations from your results.

Submission Checklist

  • Power BI report file (.pbix).
  • Screenshot of the completed report.
  • List of DAX measures used.
  • Brief explanation of data cleaning and modeling.
  • Three findings supported by the report.
  • Confirmation that access to student details has been considered.

12. Knowledge Check

Question 1: Why is StudentID useful in a student performance model?

StudentID provides a consistent identifier for linking records and distinguishing students when names are duplicated or formatted differently.

Question 2: How is Overall Percentage calculated?

Divide total marks obtained by total maximum marks and format the result as a percentage, provided this method matches the institution's assessment rules.

Question 3: Which visual is useful for comparing average marks by subject?

A bar chart is a suitable choice because it makes comparisons between subject categories easy to read.

Question 4: Why should missing marks be investigated?

Missing marks may represent unrecorded, incomplete, or inapplicable assessments. Treating them as zero without checking can distort results.

Question 5: Why must student-level report access be controlled?

Academic records are sensitive. Permissions and appropriate security controls help ensure that only authorized users can view detailed records.

Summary

In this project, you learned how to create a student performance report in Power BI. You explored data preparation, modeling, DAX measures, visualizations, slicers, validation, and student data protection.

  • Clean and validate academic data before analysis.
  • Use suitable measures to summarize marks and attendance.
  • Compare subjects and classes with clear visualizations.
  • Test filters and interactions to confirm accurate results.
  • Protect sensitive student information when sharing reports.

A useful academic dashboard presents accurate evidence, supports informed decisions, and respects student privacy.