Project 1

T20 Cricket Top 5 Player Analysis

Overview

The T20 Cricket dataset was downloaded from the ESPN Cricket website and comprised 4 files:

– players.csv
– match_summary.csv
– batting_summary.csv
– bowling_summary.csv

Process

1. Import files into PowerBI

2. Rename files

dim_players
dim_match_summary
fact_batting_summary
fact_bowling_summary

3. Clean/Transform Tables

Reviewed each table in the Power Query Editor and cleaned/transformed as follows:

a. dim_players

– use first row as headers

– remove (c) in ‘name’ column using Extract -> Text before delimiter

– Trim text (removes extra spaces if they exist)

– remove duplicates

b. dim_match_summary

– add new column ‘type of match’

before 22/10/22 was qualifier, after was Super 12

– add conditional column

– change type to Text

= Table.AddColumn(#”Changed Type”, “stage”, each if [matchDate] < #date(2022, 10, 22) then “Qualifier” else “Super 12”)

c. fact_batting_summary

– rename ‘teaminnings’ to ‘team’

– rename ‘4s’ to ‘fours’ etc

– rename ‘out/not out’ to ‘out’

– convert to boolean 1/0

– use replace values

= Table.ReplaceValue(#”Replaced Value”,”not_1″,”0″,Replacer.ReplaceText,{“out”})

– remove (c) in ‘batsmanName’ column using Extract -> Text before delimiter

– Trim text (removes extra spaces if they exist)

– add new column ‘boundaryRuns’

– convert to whole number

= ([fours]*4 + [Sixes]*6)

d. fact_bowling_summary

– rename ‘bowlingTeam’ to ‘team’

– rename ‘0s’ to ‘zeros’, ‘4s’ to ‘fours’ etc

– add new column ‘number of balls’

needed for averages (deals with part overs)

– step 1 convert ‘overs’ to ‘overs’ & ‘part overs’ (split column by delimiter)

– replace null in new column with 0

– step 2 add custom column

– convert to whole number

= Table.AddColumn(#”Replaced Value”, “balls”, each [overs.1]*6 + [overs.2])

– add new column ‘boundaryRuns’

– convert to whole number
= ([fours]*4 + [Sixes]*6)

4. Model View

a. Missing relationships between dim_players and fact_batting_summary and fact_bowling_summary identified.
These are required to link player information and stats. One to Many relationshipd created between

– dim_players/name to fact_bowling_summary/bowlerName
– dim_players/name to fact_batting_summary/batsmanName

b. Created Display Folders to group relevant measures i.e. Batting or Bowling related

5. Report View

Create DAX Key_Measures (custom dynamic calculations) to enable Dashboard refinements. For example:

– Batting Avg = DIVIDE([Total Runs],[Total Innings Dismissed],0)
– Batting Position = ROUNDUP(AVERAGE(fact_batting_summary[batting_pos]),0)
– Boundary % = DIVIDE(SUM(fact_batting_summary[Boundary runs]),[Total Runs],0)
– Total Innings Bowled = DISTINCTCOUNT(fact_bowling_summary[match_id])
– Dot ball % = DIVIDE(SUM(fact_bowling_summary[zeros]), SUM(fact_bowling_summary[balls]),0)

6. Create Dashboard

High Level Plan

– 5 tabs: Opener, Middle Order, Tail End, All Rounders, Bowlers

– Only 5 players will be listed for each category based on defined criteria.
For example, Openers

Batting avg > 30
Strike Rate > 140
Innings Batted > 3
Boundary % > 50%
Batting Position < 4

– Area chart and scatter graph will show combined values of Top 5 players. When player selected only that player stats will be shown (see image below)

– All Rounders tab will show both batting and bowling dataset

– Creation and Refinement

Dashboard look and feel refined as issues identified. Refinements included:

– Positioning of each object was important to prevent ‘jumping’ when moving between tabs
– Attention to detail when naming columns and measures
– Keeping the dashboard uncluttered and intuitive. Using tooltips definitely helped

Summary

This was a very interesting project as there were many aspects I had not considered at the beginning of the project. I’m a fan of cricket but I had not realised how much data there was.

The most frustrating element was not being able to give everyone access to the dashboard so they could interact with it. Limitations of my Microsoft account! Unlike Tableau.

My Tableau Dashboard can be see here which was developed in Project 2.

Links

ESPN Cricket

codebasics