cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rajveer1117 Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User IV
Super User IV

Re: Minimum of Values followed by Average

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

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Minimum of Values followed by Average

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

rajveer1117 Regular Visitor
Regular Visitor

Re: Minimum of Values followed by Average

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

Super User IV
Super User IV

Re: Minimum of Values followed by Average

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

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors