Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Value Avaerage

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.

1 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]))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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]))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.