Forum Discussion

wrwillits's avatar
wrwillits
Helper III
9 years ago
Solved

averagex

I am trying to calcualte the daily average of flight hours for an aircraft that has multiple flights per day for a number of days (the aircraft doesn't fly every day). I am first summing the hours fl...
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    wrwillits

     

    Hi, Try this please:

     

    1. Create a measure to count the days between the range selected in Date Slicer (Plus 1)

     

     

    DayBetweenFirst&LastDaySelected+1 =
DATEDIFF ( MIN ( AirCraft[Date] ), MAX ( AirCraft[Date] ), DAY ) + 1

     

    2. Create a measure to calculate the Average Daily Hour

    AverageDailyHour =
DIVIDE ( SUM ( AirCraft[Hours] ), [DayBetweenFirst&LastDaySelected+1] )

     

     

Sean's avatar
Sean
Community Champion
9 years ago

Can you post the results you are expecting?

Does this get you what you want?

Average Per Day MEASURE =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Aircraft] )
    ),
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Aircraft] )
    ),
    0
)

 

  • wrwillits's avatar
    wrwillits
    Helper III
    9 years ago

    The result I'm trying to get to is:

     

    Row Labels1/1/20161/2/20161/3/20161/4/20161/5/20161/6/20161/7/2016Grand TotalAverage
    N111PD680120011375.29

     

    The objective is to total the number of hours per day and then average the number of daily hours per period (the MIN and MAX that the user selects). For this example the MIN is 1/1/2016 and the MAX is 1/7/2016. The days where there are no hours flown there actually isn't any data in the aircraft's utilization table (dates 1/3/2016, 1/5/2016 and 1/6/2016 in this example). So in order to address this what I have done is created a calendar table and associated it with the aircraft's utilization table to include the dates where there are no hours flown. That then adds the all the dates, but then the days with no hours flown are blank in the "Hours" column.

    • wrwillits's avatar
      wrwillits
      Helper III
      9 years ago

      Here's an actual visual of the PowerBI dashboard that I'm developing:

       

       

      Obviously I am working with much more detailed data than what I presented as the example. You can see at the top the user can use the date scroller to identify the specific dates he wants to review (MIN and MAX). The data then refreshes with the new utilization charts (hours and cycles), and the hourly and cyclic daily averages are supposed to show up in the cards to the right.

       

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        wrwillits

         

        Hi, Try this please:

         

        1. Create a measure to count the days between the range selected in Date Slicer (Plus 1)

         

         

        DayBetweenFirst&LastDaySelected+1 =
DATEDIFF ( MIN ( AirCraft[Date] ), MAX ( AirCraft[Date] ), DAY ) + 1

         

        2. Create a measure to calculate the Average Daily Hour

        AverageDailyHour =
DIVIDE ( SUM ( AirCraft[Hours] ), [DayBetweenFirst&LastDaySelected+1] )