Calculating a Per Day Rate Based on Number of Days Worked

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