Calculate difference in times and show average of that by another column

[Gattis]

Here is what I'm working with Dsp and Ons are in time format, the data set has over 1M records

Unit               Dsp               Ons

Unit 1            00:51:58        00:58:10

Unit 2            13:28;40        13;29:20

Unit 1            18:22:36        18:29:32

I want to find the difference between DSP and ONS and display the average time per Unit on a bar chart.

I have spent hours searching with no answer.

Thanks in advance

[Greg_Deckler · Accepted solution]

If they are truly date/time data types then you can simply subtract them to find the difference. Date time values are just decimal values where the integer portion is the number of days since December 30th, 1899 and the decimal portion is the time component from minight.

[Greg_Deckler · Accepted solution]

If they are truly date/time data types then you can simply subtract them to find the difference. Date time values are just decimal values where the integer portion is the number of days since December 30th, 1899 and the decimal portion is the time component from minight.

[Gattis]

Ok,  That worked great.  Now how can I find the average of the times for each unit on a bar chart?

Now I have:

Unit            response time

unit 1          00:01:25

unit 2          00:04:24

unit 1          00:05:25

unit 3          00:06:35

unit 1          00:07:23

Each Unit has many entries and I need to find the average response time for each Unit

[Greg_Deckler · Accepted solution]

Yeah, unfortunately that last part has been a problem in Power BI for forever. Best thing that you can probably do is to have a difference column set to a decimal type. Use that for your average. Then, create a measure that also averages that column, AVERAGEX('Table',[Diff]) and then format that into a time/duration type using DAX and put it into a tooltip.  This looks like a measure aggregation problem. 

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486