Calculating a Per Day Rate Based on Number of Days Worked

[Big___Z]

This has been driving me crazy, on the surface it should be quite simple but I simply can't get it working.

Basically, I want to work out the number of service desk tickets per day an agent completes, with the number of actual days they worked being factored in for any given month so this metric isn't skewed if the agent was off for a week for example, or that a national holiday doesn't skew the results either.

Ultimately, I want a matrix table with the rows to be the agent name, and then the columns being the month name, with the values being the tickets per day the agent closed. I'll have a slicer to switch between different years for the dataset. Unfortunately I will not be able to share the data so I'll do my best to describe the issue.

The way things are setup are:

• I have CSV export from the ticketing system and I've configured a whole bunch of reports off this data already. This contains all the ticket information (Ticket ID, agent assigned, closure date and much more). All information about a ticket is contained within a single row. This table is called 'Tickets'.
• I have a small table with month names and an index column to sort the month names. This is called 'Month Name Closed'.
• The 'Month Name Closed' table has a relationship to another table called 'Calendar Closed' which is a full calendar table, which in turn has a relationship to the 'Closure Date' column in the 'Tickets' table. I'm aware this is a bit awkward but I found I had to do this to get some of the other reports to show the month names in different ways.
• I then have an Excel doc that contains the number of days each agent actually worked in any given month, spanning from the start of 2022. I have two versions of this table in two worksheets within this doc (simply through trying to work out the best way of working with this data). The first structures the data as follows:

Working Days v1

	Agent 1	Agent 2
Jan-22	20	18
Feb-22	20	20

The second version of this table is structured as below (it's just transposed):

Working Days v2

	Jan-22	Feb-22
Agent 1	20	20
Agent 2	18	20

There's about 12 agents and the dates run from 2022 to the end of 2023. The data is imported into PowerBI as 'Working Days' for v1 and 'Transposed Working Days' for v2.

Perhaps a critical thing to note is that although the dates show Jan-22, etc, in the spreadsheet, the actual data is 01/01/2022, 01/02/2022, (UK date format) etc.

So, my problem is that I simply can't work out how to calculate the tickets per day metric for a month, while factoring in the number of days actually worked, and to get it displayed in a matrix table, similar to the below:

	January	February
Agent 1	5.4	5.2
Agent 2	6.8	6.2

I will have a slicer so I can switch between different years within the dataset.

So how do I do this?

[Big___Z]

@jpessoa8 I can confirm that this has worked. I'll need to give some thought to how I manage the working days data for the future, but by putting it into 3 columns was the way to do this. Thanks so much for your help!

[Big___Z]

Sorry for the delay in replying, but thanks for your input. I was coming to the conclusion that it might be the structure of the working days sheet that was the root of the issue. I'll take a look into this today and will let you know how I get on. Again, thanks for the reply, it's appreciated.

[jpessoa8 · Accepted solution]

Hello @Big___Z ,

Based on your description, the main issue might be the way you've built you're datamodel, mainly the WorkingDays v1 and WorkingDays v2. Since you either have Months or Agents in the columns of these tables, you cannot create a relationship with the rest of the model and in the worst case scenario you would need to create a measure for each Agent (not an option in my opinion).

In my opinion, the solution should be having the Working days with 3 columns, one for Agent, one for the Month and another with the number of working days like this :

With this scenario and also adding a table with the list of Agents, you could:

• relate the Date from WorkingDays Table to the Date in the Calendar Table
• relate the Agents from WorkingDays Table to the new Agents Table
• relate the Agents table to the Agent in the Tickets Table

Something like this :

Having this, you will only need 3 simple measure to get the #Tickets per Day

#Tickets = sum(Tickets[Tickets])

#WorkingDays = SUM(WorkingDays[Days]) 

#Tickets per Day = DIVIDE( [#Tickets] , [#WorkingDays], 0 )

And you will get this outcome:

Here is the link for the PowerBI with dummy data that I've created : https://we.tl/t-4AUCbo6OU2

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa