Cyber Crime Trend Analysis in Power BI

  Cyber Crime Trend Analysis in Power BI

Problem Statement:

Cybercrime is an emerging threat in the digital age, showing varied growth patterns across Indian states. This project focuses on analyzing cybercrime trends over six years by transforming structured wide-format data using Power BI’s Unpivot Columns method. It enables a time-series analysis to draw actionable insights on crime growth and law enforcement effectiveness.

---

Dataset Overview:

Column Name	Description
State/UT	Indian State/UT
2017 to 2022	Year-wise cybercrime cases (wide format)
Mid-Year Projected Population	2022 population (in lakhs)
Rate of Total Cyber Crimes (2022)	Per lakh population rate of crimes in 2022
Chargesheeting Rate (2022)	% of total cases that were chargesheeted

---

Step-by-Step Instructions Using Unpivoting

---

Step 1: Load the Dataset

1. Open Power BI Desktop.

2. Go to Home > Get Data > Excel/CSV and load your dataset.

3. Click on Transform Data to open the Power Query Editor.

---

Step 2: Clean and Prepare Data in Power Query

Rename Columns (Optional for Clarity):

• State/UT → State

• Mid-Year Projected Population (in Lakhs) → Population_2022_Lakhs

• Rate of Total Cyber Crimes (2022) → CrimeRate2022

• Chargesheeting Rate (2022) → ChargesheetRate2022

---

Step 3: Perform Unpivot Operation

1. Select columns 2017 to 2022 (multi-select).

2. Right-click → Unpivot Columns.

3. Rename the new columns as:

   • Attribute → Year

   • Value → Total_Cyber_Crimes

---

Step 4: Convert Data Types

• Year: Whole Number

• Total_Cyber_Crimes: Whole Number

• Ensure all other numeric columns are correctly typed.

---

Step 5: Load Data

• Click Close & Apply to load cleaned data into Power BI model.

---

Visualizations to Create

Visual Type	Use
Line Chart	Year-wise crime trend for each State
Clustered Column Chart	Total Cyber Crimes per year per state
Bar Chart	Top 10 states by Cyber Crimes in 2022
Map Visual	State-wise CrimeRate2022 (heatmap)
Scatter Plot	Compare population vs. crimes in 2022
Cards	KPIs like Total Crimes, Average Crime Rate, Highest Charge-sheeting Rate

---

Create DAX Measures

---

1️⃣ Total Crimes Across All States

TotalCrimes = SUM('Table'[Total_Cyber_Crimes])

---

2️⃣ Average Crime Per Year

AverageCrimesPerYear = AVERAGEX(VALUES('Table'[Year]), [TotalCrimes])

---

3️⃣ Trend Calculation (Optional)

Trend_Change = 
VAR Current = MAX('Table'[Total_Cyber_Crimes])
VAR Prev = 
    CALCULATE(
        MAX('Table'[Total_Cyber_Crimes]),
        FILTER('Table', 'Table'[Year] = MAX('Table'[Year]) - 1 && 'Table'[State] = EARLIER('Table'[State]))
    )
RETURN
IF(Current > Prev, "Increase", IF(Current < Prev, "Decrease", "Stable"))

---

Research Questions for Student Exploration

---

🔹 RQ1: How has cybercrime evolved over the 6-year period in each state?

• Use line chart with Year on X-axis and Crime numbers on Y-axis.

---

🔹 RQ2: Which states reported the highest cybercrime rate relative to population in 2022?

• Use CrimeRate2022 in a bar or map chart.

---

🔹 RQ3: Are states with large populations reporting higher cybercrime incidents?

• Scatter plot: X → Population_2022_Lakhs, Y → 2022 Crime cases.

---

🔹 RQ4: Which states have shown consistent increase/decrease in cybercrimes?

• Add Trend_Change and use matrix or color-coded charts.

---

🔹 RQ5: What is the relationship between cybercrime numbers and charge-sheeting rate?

• Scatter plot or tooltip analysis with ChargesheetRate2022.

---