Average & achieve target

[majid154a]

Hello experts,

I have delivery company data that includes metrics such as minutes for the driver to reach the restaurant, minutes for the driver to deliver the order, and minutes for the driver to reach the customer.

First question: What is the most suitable method to calculate the average of these metrics for use in building charts?

Second and more important question: The company has specific targets for drivers, for example, the target for a driver to pick up an order from a restaurant in Zone A is 20 minutes, and in Zone B it is 15 minutes. How can I determine if the driver has achieved the target for each zone and what is the appropriate chart to evaluate their performance?

Note that targets may change based on conditions such as peak times and end of month. How can targets be dynamically adjusted?

Attached are some data for reference.

Zone	OrderTime	MinutesToPickup	MinutesToArriveAtCustomer	MinutesToHandOver
A	3/1/2023 2:43:42 AM	8.43	9.56	0.27
A	3/1/2023 2:41:27 AM	6.41	4.39	3.06
A	3/1/2023 2:37:06 AM	9.34	13.73	0.05
B	3/1/2023 2:36:49 AM	9.63	13.09	0.04
C	3/1/2023 2:35:29 AM	4.24	12.33	0.05
B	3/1/2023 2:33:06 AM	8.18	13.21	0.08
C	3/1/2023 2:30:42 AM	6.89	11.42	0.05
C	3/1/2023 2:30:20 AM	9.32	8.76	0.01
C	3/1/2023 2:28:50 AM	8.59	11.13	0.03
A	3/1/2023 2:26:31 AM	9.37	5.65	0.03
C	3/1/2023 2:25:55 AM	17.4	9.4	0.08
C	3/1/2023 2:25:14 AM	19.23	10.01	0.07
A	3/1/2023 2:25:08 AM	8.36	13.24	5.57
B	3/1/2023 2:22:17 AM	17.19	24.71	0.91
A	3/1/2023 2:20:14 AM	9.34	13.73	0.05
B	3/1/2023 2:19:40 AM	9.63	13.09	0.04
B	3/1/2023 2:15:56 AM	26.95	11.61	0.03
A	3/1/2023 2:15:51 AM	7.29	6.04	3.11
C	3/1/2023 2:14:36 AM	21.87	2.38	1.79
C	3/1/2023 2:14:16 AM	8.45	13.12	0.19
C	3/1/2023 2:13:47 AM	10.64	3.87	0.06
A	3/1/2023 2:12:25 AM	3.38	7.99	1.11
A	3/1/2023 2:12:19 AM	8.48	5.92	1.67

Thank you.

[Anonymous · Accepted solution]

Hi @majid154a ,

Based on my testing, for the first question, please try the following methods:

1.Create the measure to calculate the average for each metrics.

Average MinutesToPickup = AVERAGE('Table'[MinutesToPickup])

Average MinutesToHandOver = AVERAGE('Table'[MinutesToHandOver])

Average MinutesToArrive = AVERAGE('Table'[MinutesToArriveAtCustomer])

2.Drag the measures to the column chart.

For the second question, create the new measure to filter the target for each zone.

TargetMinutesToPickup = 
var _pickupminute = SELECTEDVALUE('Table'[MinutesToPickup])
var _zone = SELECTEDVALUE('Table'[Zone])
RETURN
IF(_pickupminute <= 20 && _zone = "A", "Yes", 
    IF(_pickupminute < 15 && _zone = "B", "Yes", "No")
)

Achieve goal = 
var _achievegoal = CALCULATE(COUNTROWS('Table'), FILTER('Table', [TargetMinutesToPickup] = "Yes"))
var _goal = CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Zone]))
var result = DIVIDE(_achievegoal, _goal)
RETURN
IF(result = 0, 0 , result)

Then, drag the measure to the column chart.

Best Regards,

Wisdom Wu

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