Power BI Report On Crimes Against Children From 2001 To 2012

Power BI Report On  Crimes Against Children From 2001 To 2012

Open Crimes Against Children Dataset Open Dataset

Power BI Research Questions and Data Transformation Steps

Step 1: Data Preparation (Unpivoting First)

Unpivoting Steps in Power BI

Since the dataset has multiple year columns (2001, 2002, 2003, ... 2012), we must

 first unpivot the data to make it suitable for analysis.

Steps to Unpivot the Data in Power BI

1. Load Data into Power BI:

   • Click Home → Get Data → Excel Workbook and select your dataset.
   • Choose the sheet and click Transform Data to open Power Query Editor.

2. Unpivot the Year Columns:

   • Select only the STATE/UT and CRIME HEAD columns.
   • Click Transform → Unpivot Other Columns (this will convert year columns 
   • into a single column).

3. Rename Columns:

   • Rename the Attribute column to Year.
   • Rename the Value column to CrimeCount.

4. Close & Apply to load the transformed data into Power BI.

---

Step 2: Research Questions for Power BI Report

1. Total Crimes Over the Years

• Research Question: How has crime against children changed over time?
• Steps:
  • Create a line chart with Year on the X-axis and Total Crime on the Y-axis.
  • Apply filters for specific STATE/UT and CRIME HEAD.

2. State-Wise Crime Comparison

• Research Question: Which states have the highest and lowest crime rates?
• Steps:
  • Use a bar chart with STATE/UT on the X-axis and Total Crime on the Y-axis.
  • Sort data in descending order.
  • Apply a slicer to filter by Year.

3. Crime Trend by Category

• Research Question: How has each type of crime evolved over time?
• Steps:
  • Use a line chart with Year and CrimeCount.
  • Apply a slicer for CRIME HEAD.

4. State with Maximum Crime in a Given Year

• Research Question: Which state reported the highest crime in a selected year?
• Steps:
  • Use a table visualization sorted by CrimeCount in descending order.
  • Apply a slicer for Year.

5. Crime Percentage by State

• Research Question: What is the percentage contribution of each state to total crimes?
• Steps:
  • Create a pie chart with STATE/UT and Crime Percentage.
  • DAX Measure:

    CrimePercentage = DIVIDE(SUM(CrimeData[CrimeCount]), 

  • CALCULATE(SUM(CrimeData[CrimeCount]), ALL(CrimeData))) * 100

  • Apply filters for specific years.

6. Year-over-Year Crime Growth

• Research Question: How has crime changed compared to the previous year?
• Steps:
  • Create a line chart with Year and Crime Growth.
  • DAX Measure for YoY Growth:

    YoY_Crime_Growth = 
    VAR PrevYearCrime = CALCULATE(SUM(CrimeData[CrimeCount]),

  •  PREVIOUSYEAR(CrimeData[Year]))
    RETURN IF(NOT ISBLANK(PrevYearCrime),

  •  (SUM(CrimeData[CrimeCount]) - PrevYearCrime) / PrevYearCrime, 

  • BLANK())
    

  • Format it as a percentage.

7. Top 3 Crime Types Per Year

• Research Question: What are the top 3 most reported crimes each year?
• Steps:
  • Use a table visualization.
  • DAX Measure for Ranking Crime Types:

    RankCrime = RANKX(FILTER(ALL(CrimeData), CrimeData[Year]

  •  = SELECTEDVALUE(CrimeData[Year])), SUM(CrimeData[CrimeCount]),, 

  • DESC, DENSE)
    

  • Apply a filter to show only the top 3 crime types per year.

8. Forecasting Future Crime Trends

• Research Question: What will be the expected crime rates in the next 5 years?
• Steps:
  • Use Power BI’s Forecasting feature in the Analytics pane.
  • Extend the trend line for 5 years.

9. Crime Seasonality Analysis

• Research Question: Are there seasonal patterns in crime data?
• Steps:
  • Use Power BI’s Moving Average function.
  • Analyze fluctuations in crime trends.

10. High-Crime vs. Low-Crime States Over Time

• Research Question: How do high-crime states compare to low-crime states over time?
• Steps:
  • Create a line chart comparing high-crime vs. low-crime states.
  • Use a slicer to explore specific states.

---

Key Insights

• Unpivoting the dataset at the beginning ensures a structured format for analysis.
• DAX measures help with percentage calculations, ranking, and year-over-year growth.
• Power BI's forecasting can provide predictions for future trends.
• Slicers and filters make reports more interactive.
  
  
  

Future Prediction on Cyber Crimes Against Children Using Power BI

• Power BI provides built-in forecasting capabilities that allow users to predict future trends in crime data. In this case, we aim to forecast cyber crimes against children using Power BI’s forecasting feature 

  ---

🔹 Step 1: Ensure Data is in the Right Format

• Before applying forecasting, ensure that the dataset is correctly formatted:

  ✅ Year Column → Must be Whole Number (Convert from Text if needed).
  ✅ CrimeCount Column → Must be Whole Number or Decimal.

  📌 If the Year column is in text format, change it by:

  • Selecting the Year column.
  • Going to Column Tools → Changing Data Type to Whole Number.

  ---

🔹 Step 2: Create a Line Chart for Forecasting

• 1. Open Power BI Report View.
  2. Insert a Line Chart (From the Visualizations Pane).
  3. Drag ‘Year’ to the X-axis and ‘CrimeCount’ to the Y-axis.
  4. Ensure the X-axis is set to Continuous:
     • Click on the chart.
     • Go to Format Pane → X-Axis → Type → Set to Continuous.

  ---

🔹 Step 3: Enable Forecasting in Power BI

• 1. Click on the Line Chart to activate it.
  2. Go to the Analytics Pane (Magnifying Glass Icon 🔍 in the Right Panel).
  3. Scroll down and find Forecast.
  4. Click "Add" to enable forecasting.

  ---

🔹 Step 4: Adjust Forecast Settings

• • Forecast Length → Set the number of future years (e.g., 5 years).
  • Confidence Interval → Default is 95%, but can be adjusted.
  • Seasonality → If crime follows a pattern, set:
    • 12 for Monthly Data
    • 1 for Yearly Data

  ---

🔹 Step 5: Apply & View Forecast

• 1. Click Apply to generate the forecast.
  2. Power BI will display future crime trends as a dotted line.

  ---

Final Insights

• ✅ Power BI’s built-in forecasting is easy and quick for analyzing crime trends.
  ✅ Use "Seasonality" if crime rates follow a yearly pattern.
  ✅ Adjust "Confidence Interval" to refine predictions.

  
  

Why Use a Confidence Interval in Forecasting? 

A Confidence Interval (CI) represents the range within which the actual future values are expected to fall, with a certain probability. In Power BI, the default CI is 95%, but you can adjust it.

🔹 Purpose of Confidence Interval in Forecasting

Indicates Prediction Reliability ✅

A higher CI (e.g., 95%) means the forecast is more cautious, covering a broader range.

A lower CI (e.g., 80%) means a narrower range, showing a more confident but riskier prediction.

Shows Uncertainty in Data 📊

If past crime data fluctuates a lot, the CI range will be wider.

If trends are stable, the CI range will be narrower.

Helps in Decision-Making 

Higher CI (e.g., 99%) → Used when avoiding underestimation is crucial (e.g., planning law enforcement resources).

Lower CI (e.g., 80%) → Used when precision is preferred over a broad range.