In this article, I will guide you through creating your own Power BI dashboard for tracking tickets. While seemingly mundane, tracking tickets is key to the success of the customer service and IT department and impacts the success of a company.

If you are looking for an in-depth review of the KPIs for your customer service team, you can check out the article here. 

Below the dashboard that what we will build together.

This dashboard has three main parts: tickets by month, service level, and detail by representative.

Tickets tracking by month in Power BI

This first chart is the standard chart for ticket tracking and presents how fast the team is processing the incoming tickets.

Service Level

The Service level chart focuses on the total time spent to process a ticket. Specifically, the 'great service ratio' is the percentage of tickets closed in 2 days or less. On the other hand, the 'poor service ratio' is the percentage of tickets closed in 8 days or more.

Our example company improves its service in the first semester until May, then performance worsens. In the last 2 months of the year, half of the tickets take 8 days or more to be processed.

Detail by agent

Last part of the chart focuses on the customer service agents

Vincent is struggling most: his tickets wait an average of almost 4 days before he starts processing them. Moreover, he has the lowest percentage (38%) of tickets treated within 2 days of creation.

Loading the ticket tracking data in Power BI

Let's now start loading the data. You can download the sample data from here.

The source data includes 2 tables; the first table is a list of the changes in ticket status. For example, ticket number 50 moves to the status 'processing' on January 7^th.

The second table has a list of all the tickets, along with the agent processing them.

Now let's move on to the fun part of building the dashboard.

Tickets tracking by month

The formulas for the first chart are the easier ones: we build two measures, one for counting the number of tickets created and another one for the tickets closed.

Here are the measures:

Tickets created = 
CALCULATE(COUNT('ticketsData'[ticketID]),
'ticketsData'[status]="created")

Tickets closed = 
CALCULATE(COUNT('ticketsData'[ticketID]),
'ticketsData'[status]="closed")

We use CALCULATE to choose a perimeter for the COUNT function. The perimeter for tickets created is all tickets with the status of 'created'.

Let's now move on to the Service Level.

Service Level

To calculate the service level ratios (great service ratio and poor service ratio), we first need columns that tell us for each ticket:

• wait time (the time between the user creating the ticket, and the agent starting the processing)

• processing time (the time between the agent starting the processing, and the agent moving the ticket to closed)

• total time, which is the sum of wait and processing time

Below are the formulas for these calculated columns. These formulas should be entered in the ticketsInfo table, the table with the number of tickets and agents.

created date = LOOKUPVALUE(ticketsData[date],ticketsData[ticketID],[ticket],ticketsData[status],"created")

processing started date = LOOKUPVALUE(ticketsData[date],ticketsData[ticketID],[ticket],ticketsData[status],"processing")

closed date = LOOKUPVALUE(ticketsData[date],ticketsData[ticketID],[ticket],ticketsData[status],"closed")

wait time (days) = DATEDIFF([created date],[processing started date],DAY)

processing time (days) = DATEDIFF([processing started date],[closed date],DAY)

total time (days) = [wait time (days)]+[processing time (days)]

And this is what the table looks like with the added columns:

We are now ready to calculate the great service ratio (% of tickets treated in less than 2 days) and the poor service ratio (% of tickets treated in more than 8 days).

Here are the formulas:

Great service ratio = COUNTX(
FILTER('ticketsInfo',[total time (days)]<=2), [ticket]) /
COUNT('ticketsInfo'[ticket])

Poor service ratio = COUNTX(
FILTER('ticketsInfo',[total time (days)]>=8), [ticket]) /
COUNT('ticketsInfo'[ticket]) 

And the result!

Detail by agent

We already have all the measures for the Detail by agent table, except one: the number of tickets waiting and in process. This is the formula:

Tickets waiting and in process = 
COUNTX('ticketsInfo',[created date])-
COUNTX('ticketsInfo',[closed date])

You now have a full ticket tracking dashboard! I hope this was helpful.

Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.