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 flown per day, and then averaging the sum of the daily hours flown over a period of time (there's a Min and Max Date that the user selects to perform the evaluation). Here's some sample data:

 

AircraftDateHours
N111PD1/1/20162
N111PD1/1/20163
N111PD1/1/20161
N111PD1/2/20164
N111PD1/2/20163
N111PD1/2/20161
N111PD1/4/20165
N111PD1/4/20161
N111PD1/4/20164
N111PD1/4/20162
N111PD1/7/20165
N111PD1/7/20166

 

To sum the hours per day I am using the following formula (agian, the user selects the MIN and MAX Dates):

 

CumulativeDailyHours =

CALCULATE(

                   sum(Table[Date]),

                   FILTER(

                              ALL (Table[Date],

                                      Table[Date] <= MAX(Table[Date] &&

                                      Table[Date] > MIN(Table[Date])

                   )

)

 

That seems to be working (please let me know if you think I'm missing something), but when I try to come up with a daily average I get a "(Blank)" in the card that I am using to show the Average Daily Hours Flown. Here's the calcualtion for the average hours that I am using:

 

AvgDailyHours =

AVERAGEX (Table, [CumulativeDailyHours])

 

I'm thinking that AVERAGEX is having a problem with the fact that there aren't flights every day and so it doesn't know what to do with the non-flying days, but I have tried to do various ways to fill in the non-flying days with a "0" (IF(ISBLANK(.....), but even that doesn't seem to work.

 

This should be realtively easy calculation but for some reason I can't seem to get it to work.

 

Thanks

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

     

     

5 Replies

  • 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.