Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        7 years ago

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