Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Big___Z
New Member

Calculating a Per Day Rate Based on Number of Days Worked

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 1Agent 2
Jan-222018
Feb-222020

 

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

 

Working Days v2

 Jan-22Feb-22
Agent 12020
Agent 21820

 

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:

 

 JanuaryFebruary
Agent 15.45.2
Agent 26.86.2

 

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

 

So how do I do this?

1 ACCEPTED SOLUTION
jpessoa8
Continued Contributor
Continued Contributor

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 :

 

jpessoa8_0-1700854215720.png

 

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 :

 

jpessoa8_1-1700854353716.png

 

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:

 

jpessoa8_2-1700854445670.png

 

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

 

View solution in original post

3 REPLIES 3
Big___Z
New Member

@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
New Member

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
Continued Contributor
Continued Contributor

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 :

 

jpessoa8_0-1700854215720.png

 

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 :

 

jpessoa8_1-1700854353716.png

 

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:

 

jpessoa8_2-1700854445670.png

 

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors