Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Ok I am stumped on this one, I have an excel spreadsheet that includes these columns
AssetUUID | timeHappenedUTC | Title |
12580 | 06/01/2021 3:14:14PM | SOS |
12580 | 06/01/2021 3:15:14PM | Emergency |
12580 | 06/01/2021 3:15:45PM | SOS Cancel |
1580 | 08/01/2021 2:25:30AM | LOGIN |
12558 | 07/01/2021 4:20:15AM | SOS |
12558 | 07/01/2021 4:35:15AM | SOS Cancel |
The Goal is to look at the average time to cancel so I build a calculated column to calculate the date difference between 'SOS' and 'SOS Cancel' that looks like this
Open to close = VAR SOSEmergency = CALCULATE(MAX('User Data'[timeHappenedUTC]),FILTER('User Data',[title] = "SOS"))
VAR Cancel = CALCULATE(MAX('User Data'[timeHappenedUTC]),FILTER('User Data',[title] = "SOS Cancel"))
RETURN DATEDIFF(SOSEmergency,Cancel,MINUTE
I've been able to validate the times are accurage I have these times appearing between 1 and 57 minutes so that works.
I am having a huge issue trying to get an average time from open to close: anything I do with averages show up as the highest time closed of 57 minutes, no matter what I do I seem to be missing the average. any ideas?
@pgiorgi1 Can you show us what measure did you try to get the Average?
Maybe a picture of a table and the result you currently have?
Since you already have a calculated column that seems to work, you could also try this :
AVERAGEX(
VALUES( Table[AssetUUID] ),
Table[YourCalculatedColumn]
)
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |