minimum then average minmum value of time deminsion

[Anonymous]

Can any buddy help me acheiving the below results.

Engineer Travel Date Travel time
1044         4/14/2017 16:41:57
1044         4/18/2017 16:21:58
1044          4/18/2017 16:21:28
1044          4/19/2017 16:28:42
1044          4/19/2017 8:41:37
1044         4/20/2017 7:59:33
1044        4/20/2017 13:35:08
1044        4/20/2017 13:07:28
1044        4/20/2017 13:18:06
1044        4/25/2017 9:42:11
1044 4/25/2017 8:35:24
1044 4/25/2017 13:44:10
  
If remove the Travel Time the desired O/p is  
it is taking the min value of each date   
Travel Date                Travel Time 
4/14/2017                16:41:57 
4/18/2017                 16:21:28 
4/19/2017                 8:41:37 
4/20/2017                7:59:33 
4/25/2017                 8:35:24 
  
  
If we Remove Date then the o/p should be  

                                                                 Travel Time 
Its is taking the average of all the date.   11:40 

[Zubair_Muhammad]

@Anonymous

May be somehting like this

Measure =
IF (
    COUNTROWS ( VALUES ( Table1[Travel Dates] ) ) = 1,
    MIN ( Table1[Travel Time] ),
    AVERAGE ( Table1[Travel Time] )
)

[Anonymous]

The problem is taking the average as this is time field.

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Please try to use this formula. 

Measure2 =
IF (
    COUNTROWS ( VALUES ( Table1[Travel Date] ) ) = 1,
    FORMAT ( MIN ( Table1[Travel Time] ), "hh:mm:ss" ),
    FORMAT ( AVERAGE ( Table1[Travel Time] ), "hh:mm:ss" )
)

Regards,

Frank

[Anonymous]

The minimum o/p is working fine , the only problem is with average. The average is not showing correct.

Below are the senerios.

Travel Date Travel Time
4/14/2017 4:41:57 PM
4/18/2017 4:21:28 PM
4/18/2017 4:21:58 PM
4/19/2017 4:28:42 PM
4/19/2017 8:41:37 AM
4/20/2017 1:07:28 PM
4/20/2017 1:18:06 PM
4/20/2017 1:35:08 PM
4/20/2017 7:59:33 AM
4/21/2017 12:11:07 PM
4/21/2017 2:12:59 PM
4/21/2017 3:32:05 PM
4/21/2017 8:09:19 AM
4/25/2017 1:44:10 PM
4/25/2017 8:35:24 AM
4/25/2017 9:42:11 AM
4/26/2017 1:15:20 PM
4/26/2017 1:30:00 PM
4/26/2017 1:50:09 PM
4/26/2017 12:17:23 PM
4/26/2017 2:00:59 PM
4/26/2017 2:18:38 PM
4/26/2017 2:43:31 PM
4/27/2017 11:08:30 AM
4/27/2017 9:11:28 AM

Desired Result with the formula mentioned
Measure =
IF (

   COUNTROWS ( VALUES ( Table1[Travel Date]) ) = 1,

   format( MIN ( Table1[Travel Time] ,"hh:mm:ss"),format(AVERAGE(Travel Time]),"hh:mm:ss"),
format(average(table1[travle Time]),"hh:mm:ss"))

Travel date Travel Time
4/14/2017 16:41:57
4/18/2017 16:21:28
4/19/2017 8:41:37
4/20/2017 7:59:33
4/21/2017 8:09:19
4/25/2017 8:35:24
4/26/2017 12:17:23
4/27/2017 9:11:28

Average is coming as 12:50:22

where as the actual average should be 10:59:46

Can you please help me in acheiving this.

[Anonymous]

It is showing the overall average not the minimum value of average.

[Anonymous]

I also notice that it is showiing the average of overall result whereas i want the average of only Min values.

[Anonymous]

Thanks Frank and Zubair. 

[Anonymous]

Hi , i tried the suggestion which you have posted but it is not working.