Forum Discussion

majid154a's avatar
majid154a
Helper II
2 years ago
Solved

Average & achieve target

 

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.

ZoneOrderTimeMinutesToPickupMinutesToArriveAtCustomerMinutesToHandOver
A3/1/2023 2:43:42 AM8.439.560.27
A3/1/2023 2:41:27 AM6.414.393.06
A3/1/2023 2:37:06 AM9.3413.730.05
B3/1/2023 2:36:49 AM9.6313.090.04
C3/1/2023 2:35:29 AM4.2412.330.05
B3/1/2023 2:33:06 AM

8.18

13.210.08
C3/1/2023 2:30:42 AM6.8911.420.05
C3/1/2023 2:30:20 AM9.328.760.01
C3/1/2023 2:28:50 AM8.5911.130.03
A3/1/2023 2:26:31 AM9.375.650.03
C3/1/2023 2:25:55 AM17.49.40.08
C3/1/2023 2:25:14 AM19.2310.010.07
A3/1/2023 2:25:08 AM8.3613.245.57
B3/1/2023 2:22:17 AM17.1924.710.91
A3/1/2023 2:20:14 AM9.3413.730.05
B3/1/2023 2:19:40 AM9.6313.090.04
B3/1/2023 2:15:56 AM26.9511.610.03
A3/1/2023 2:15:51 AM7.296.043.11
C3/1/2023 2:14:36 AM21.872.381.79
C3/1/2023 2:14:16 AM8.4513.120.19
C3/1/2023 2:13:47 AM10.643.870.06
A3/1/2023 2:12:25 AM3.387.991.11
A3/1/2023 2:12:19 AM8.485.921.67

Thank you.

help dax
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.