averagex

[wrwillits]

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:

Aircraft	Date	Hours
N111PD	1/1/2016	2
N111PD	1/1/2016	3
N111PD	1/1/2016	1
N111PD	1/2/2016	4
N111PD	1/2/2016	3
N111PD	1/2/2016	1
N111PD	1/4/2016	5
N111PD	1/4/2016	1
N111PD	1/4/2016	4
N111PD	1/4/2016	2
N111PD	1/7/2016	5
N111PD	1/7/2016	6

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

[Sean]

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]

The result I'm trying to get to is:

Row Labels	1/1/2016	1/2/2016	1/3/2016	1/4/2016	1/5/2016	1/6/2016	1/7/2016	Grand Total	Average
N111PD	6	8	0	12	0	0	11	37	5.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]

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 · Accepted solution]

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

 

[wrwillits]

It's working! Thanks for that.

Also, I'm still a bit confused at to why AVERAGEX wouldn't work. Were the missing dates causing the issue?