Calculate the average of time data

[Anonymous]

I am aiming to create a bar chart with fiscal year on the X axis, and average response time of a vehicle on the Y axis. 

I have a date table that has the year data in, and a vehicle response time table that has the time it took a vehicle to respond to an incident. Is there a measure that I can create that sums up the response times and then averages it based upon the fiscal year so i could then show this in a bar chart?

[v-yalanwu-msft]

Hi, @Anonymous ;

If you want to find the average response time, you can use AVERAGE(), but I am not very clear about your data structure and scenario. Can you share some simple pbix after removing sensitive information, or share screenshots about your relationship and fields as well as What do you want to output?

[Anonymous]

Hi @v-yalanwu-msft , thanks for the reply. Of course.

I am using a star schema data warehouse, this is then turned into an SSAS tabular model and connected to power BI.

For my question around response times, I have a VehicleReponseTime dimesnion table, which foreign keys into my Fact table. Within the fact table there are several other dimensions FK'd in. In the VehicleReponseTime I have a row for each incident and the time in seconds it took them to respond. I want to create a bar chart that has fiscal year on the X axis (from my date dimension table) and the average response time for each year, as per the excel screenshot below:

I've not done much work with time data format, so is there also a way to convert my time in seconds to HH:MM:SS so the bar chart would look the same in PBI as the above. Also worth noting that we need to do a distinct count on the incident ID column in the fact table, not a sum, but we can sum the repsonse time in seconds. Thanks, let me know if you require more info.

[v-yalanwu-msft]

Hi, @Anonymous ;

I tested that format are 13:30:55 (hh:mm:ss) divide discount(which is whole number), and the result is "General" type which is error result.  and you mentioned that so is there also a way to convert my time in seconds to HH:MM:SS so the bar chart would look the same in PBI as the above. so  I 'am confused.

So, i want to know the three table's (date\vehicle response time\Fact Incident) relationship like belows and the TimeDifference‘s type  . 

In addition , if you use divide(), i think the [SumOfTimeDifference] can use datediff(,second). finally you could change it to HH:MM:SS type.

[Anonymous]

Hi @v-yalanwu-msft , thanks again for the reply.

My table structure is as follows:

Date table

-Fiscal Year (text)

-many other date columns - don't think necessary for this.

Fact Incident table:

-Incident ID

-fkDateID (fk from date dimension)

-fkVehicleResponseTimeId (fk from vehicle respose time dimension)

Vehicle Response Time table:

- Time Mobilised (date/time)

- Time At Scene (date/time)

- TimeDifference (date/time)

- TimeInSeconds (Whole number)

Note; time difference is the time between mobilisation and arrival at scene. Time in seconds column is the time difference displayed in seconds. I want to use either of these columns in the bar chart (both represent the same time), but want it displayed in HH:MM:SS so timedifference column may be easiest as it's already shwon this way.

Relationships: both dimesnion tables are one to many related to the fact table.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could try the measure as follows:

AverageTime =
VAR _avg =
    CALCULATE (
        DIVIDE (
            [SumOfTimeDifference],
            DISTINCTCOUNT ( 'Fact Incident'[Incident ID] ),
            FILTER (
                ALLSELECTED ( 'Date' ),
                'Date'[Fiscal Year] = MAX ( 'Date'[Fiscal Year] )
            )
        )
    )
RETURN
    INT ( _avg / 3600 ) & ":"
        & INT ( ( _avg - INT ( A1 / 3600 ) * 3600 ) / 60 ) & ":"
        & _avg
            - INT ( _avg / 3600 ) * 3600
            - INT ( ( _avg - INT ( _avg / 3600 ) * 3600 ) / 60 ) * 60

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft , i am getting the following error in the forumla about "A1": 

[v-yalanwu-msft]

Hi, @Anonymous ;

I'm sorry , change it to the following:

AverageTime =
VAR _avg =
    CALCULATE (
        DIVIDE (
            [SumOfTimeDifference],
            DISTINCTCOUNT ( 'Fact Incident'[Incident ID] ),
            FILTER (
                ALLSELECTED ( 'Date' ),
                'Date'[Fiscal Year] = MAX ( 'Date'[Fiscal Year] )
            )
        )
    )
RETURN
    INT ( _avg / 3600 ) & ":"
        & INT ( ( _avg - INT ( _avg / 3600 ) * 3600 ) / 60 ) & ":"
        & _avg
            - INT ( _avg / 3600 ) * 3600
            - INT ( ( _avg - INT ( _avg / 3600 ) * 3600 ) / 60 ) * 60

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft , thanks again.

The formula is Ok, but it won't let me add the measure to my bar chart as the value? 

The data type for the AverageTime measure is Time and format 13:30:55 (hh:nn:ss), as is the "SumOfTimeDifference" measure.

See SumOfTimeDifference measure below: 

The measure above is summing up the TimeDifference column in my tabular model, which has the following data type: 

Could you offer any more support? Thanks again for all your help so far.

[amitchandak]

@Anonymous , You need to try a measure like

calculate(divide([response time], [No of incident]), filter(allselected('Date'), 'Date'[Year] = max('Date'[Year])))

[Anonymous]

Thanks for the reply @amitchandak. I have created the following :

AverageTime = CALCULATE(DIVIDE([SumOfTimeDifference], DISTINCTCOUNT('Fact Incident'[Incident ID]), FILTER(ALLSELECTED('Date'), 'Date'[Fiscal Year] = MAX('Date'[Fiscal Year]))))

Note: "SumOfTimeDifference" is a measure that sums up the response time database column as it wouldn't allow me to use the actual column? That measure is the following:

SumOfTimeDifference = SUM('Vehicle Response Time'[TimeDifference]) 

However, the measure at the top based off your advice when added to the values of the bar chart, and fiscal year as the axis gives the error message on the visual of can't display the visual. Do you have any further support for this?

Both the measures used are date/time data type and format are 13:30:55 (hh:mm:ss) as is the DB column.

Thanks!