Value Avaerage

[Anonymous]

I need help in acheving the below result.

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

then Minimum values of the result.

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

  Then average should be  10:59:46

Can you please help me in acheiving this.

[Greg_Deckler]

OK, a few different issues here. One is dealing with aggregating time/duration. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Then you have a measure totals issue. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

So, what you are going to need essentially is a measure like:

Measure = 
VAR __tmpTable = SUMMARIZE('Table',[Travel Date],"__Min",MIN([Travel Time))
IF(HASONEVALUE([Travel Date]),MIN([Travel Time]),AVERAGEX(__tmpTable,[__Min]))

Something along those lines.

[Anonymous]

Thanks for the reply, I am getting the below error.

The expression refers to multiple column. Multiple columns cann't be converted to a scalar values

[Greg_Deckler · Accepted solution]

Sorry, couple of issues, mainly forgot my RETURN statement:

Measure = 
VAR __tmpTable = SUMMARIZE('Table',[Travel Date],"__Min",MIN([Travel Time]))
RETURN IF(HASONEVALUE([Travel Date]),MIN([Travel Time]),AVERAGEX(__tmpTable,[__Min]))

Here is the actual one that I have in my PBIX:

MyMeasure = 
VAR __tmpTable = SUMMARIZE('Table7',[Travel Date],"__Min",MIN('Table7'[Travel Time]))
RETURN IF(HASONEVALUE(Table7[Travel Date]),MIN([Travel Time]),AVERAGEX(__tmpTable,[__Min]))