How to correctly calculate the percentage of productivity

[sebastianslz]

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:

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:

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 Canceled State.

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

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:

Technical	Tickets Attended	Resolved	Canceled	% productivity
User1	41	35	6	20.8121
User2	109	100	9	55.3299
User3	47	43	4	23.8578
TOTAL	197	178	19	99.9998

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?

[v-zhangti · Accepted solution]

Hi, @sebastianslz 

You can try the following methods. [Tickets Attended] is a measure.

Measure:

Total Tickets = SUMX(ALL(Tickets),[Tickets Attended])

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

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Try this.  Simplify your Tickets attended measure to

Tickets Attended = CALCULATE(COUNTROWS(Tickets),Tickets[Status]="Resolved" || Tickets[Estado]="Cancelled")

Write this measure

All tickets attended = SUMX(VALUES(Tickets[Users]),[Tickets attended])

Now write the division measure

Meausre = divide([Tickets attended],[All tickets attended])

Hope this helps.

[sebastianslz]

Hi @Ashish_Mathur 

I have tried to perform the measurements according to what it tells me but I have reached the same result, in this case the value is 1.00, annex the example I am doing in Power BI using a table, as you can visualize the productivity of all technicians is 1.00 which is incorrect.

Are there any other changes I need to make to the extent you indicated?

[Ashish_Mathur]

Hi,

I can help only if you share the download link of the PBI file.