How to correctly calculate the percentage of productivity

Jhon Doe

New Member
Joined
May 23, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
In PowerBI I am calculating the percentage of productivity of each of the technicians who attend tickets, to calculate the productivity of each one I am using the formula "Tickets attended * 100 / Total tickets", to calculate productivity I am creating a new measure where I am executing the following formula:

Power Query:
Measure = DIVIDE(Tickets[Tickets Attended]*100,Tickets[Total Tickets])

The "Tickets Attended" I am obtaining based on a measure which its formula is as follows:

Power Query:
Tickets Attended = CALCULATE(COUNTAX(Tickets,Tickets[Status]),Tickets[Status]="Resolved" || Tickets[Estado]="Cancelled")

The only thing I am interested in having in the "Attended Tickets" are those that are in Resolved or Cancelled State.

To calculate "Total Tickets" I am using the following formula in a measure, where I use the previous measure of "Tickets Attended"

Power Query:
Total Tickets = SUMX(VALUES(Tickets[ID Ticket]),Tickets[Tickets Attended])

To give a clearer example of what I want to do in Power Bi, I have done it in Excel as follows:

1684878191062.png


I explain the formula to calculate the productivity of each Technician, for User1 is:

41*100/197 = 20.8121

The error I'm presenting when adding this measure to the table in Power BI is that all the values in the productivity of each technician results in 100. What would be the correct formula you should use?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,223,885
Messages
6,175,182
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top