• Problem Statement
• Data Gathering / Requirement
• Data Perparation
• Data Modelling
• Data Analysis Expression (DAX) Calculation
• Report
• Dashboard
• Tools, Software

We have gathered data from various sources, including sales data, product information, geography, sales representatives, and categories. The task involves data gathering, data modeling, DAX calculations, and creating visuals for the dashboard.

In this challenge, you are presented with a reliable dataset that shows the IT ticket data and Agents’ profiles for a fictitious company that you work for. You will provide your actionable insight into your organization's trends, usage patterns, systems behaviours, service level agreement etc. It will help our IT department to stay up to date with industry developments. We have gathered data from various sources, including IT Agents and Tickets. The task involves data gathering, data modeling, DAX calculations, and creating visuals for the dashboard.

• The client requires the canvas settings of the PBI report to be H:1080 - W:1920
• The client would like columns Severity and Priority to be split into 2 columns, creating an ID and classification columns. Example Severity Key column = 0 and Severity Type = Unclassified.
• The Average Resolution time is 4.5 days. The Client would like to create a new column where if the Resolution time is above 3.5 days = "Outside SLA" and if it is below "Within SLA". This will allow the client to push for all calls to stay within the limit by targeting these calls and agents.

• Column Full Name to be split into 2 columns Name and Last Name, where last name is missing to be obtained from the email address column.
• Name and Last name columns must be Capitalize first words and trimmed.
• Year/Month/Date of birth must be in one Column - data type Date.
• The client would also like to know the Age of the Agents from the Agents' DOB to 31/12/2020.

The Dataset used for this challenge was presented by FP20 Challenges and Diversity and Inclusion dataset:

Dataset:

• IT Tickets and Agents Table

Completed the Data transformation in Power Query and the dataset was loaded into Microsoft Power BI Desktop for modeling.

IT Tickets and Agents dataset is given table named:

• IT Tickets which has 97499 rows and 10 Column of observation.
• IT Agents which has 50 rows and 8 Column of observation.

Then dataset was cleaned and transformed, it was ready for data modeled.

• The IT Tickets and Agents tables as shown below:

We will create the data model connecting all tables and utilize the Calendar table that has already been set up.

Measures used in visualization are:

• Total Agents = COUNT(IT_Agents[Agent ID])
• Total Ticket = COUNT(Tickets1[ID Ticket])
• Avg. Agent Age = AVERAGE(IT_Agents[Age])
• Avg. Resolution Time = AVERAGE(Tickets1[Resolution Time (Days)])
• Avg. Satisfaction Rate = AVERAGE(Tickets1[Satisfaction Rate])
• Issue type IT Error = CALCULATE(COUNT(Tickets1[ID Ticket]),Tickets1[Issue Type]="IT Error")
• Issue type IT Request = CALCULATE(COUNT(Tickets1[ID Ticket]),Tickets1[Issue Type]="IT Request")
• Outside SLA = CALCULATE(COUNT(Tickets1[ID Ticket]),Tickets1[Resolution]="Outside SLA")
• Within SLA = CALCULATE(COUNT(Tickets1[ID Ticket]),Tickets1[Resolution]="Within SLA")
• Resolution Issue type IT Error = CALCULATE(COUNT(Tickets1[Resolution Time (Days)]),Tickets1[Issue Type]="IT Error")
• Resolution Issue type IT Request = CALCULATE(COUNT(Tickets1[Resolution Time (Days)]),Tickets1[Issue Type]="IT Request")

Date Calculation:

• DateMaster = CALENDAR(FIRSTDATE(Tickets1[Date]),LASTDATE(Tickets1[Date]))
• Month = MONTH(DateMaster[Date])
• Year = YEAR(DateMaster[Date])
• Month Name = FORMAT(DateMaster[Date],"MMM")
• Month Order = DateMaster[Date].[MonthNo]
• Quertor = QUARTER(DateMaster[Date])
• Week Day = WEEKDAY(DateMaster[Date])
• Week Day Name = FORMAT(DateMaster[Date],"DDD")
• Year = YEAR(DateMaster[Date])

Data visualization for the dataset was done using Microsoft Power BI Desktop:

View Report - Report

Tickets

Agents

Dashboard

1. Power BI

2. Power Query Editor

3. Power BI Service

4. Power BI Desktop

5. Power BI Dashboard

6. DAX

7. Excel