Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
May be somehting like this
Measure = IF ( COUNTROWS ( VALUES ( Table1[Travel Dates] ) ) = 1, MIN ( Table1[Travel Time] ), AVERAGE ( Table1[Travel Time] ) )
The problem is taking the average as this is time field.
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
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.
It is showing the overall average not the minimum value of average.
I also notice that it is showiing the average of overall result whereas i want the average of only Min values.
Thanks Frank and Zubair.
Hi , i tried the suggestion which you have posted but it is not working.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |