Average Duration Returns Incorrect Results

[obera]

I am trying to display the average duration a ticket was open in a way that can be sliced and filtered. Apologies in advance for the long explanation, and hoping someone can help me.

I have a column in my base data ('Resolution SLA'[taskslatable_business_duration]) that is total number of seconds that count towards the ticket's duration (I am not subtracting dates as there are some exclusions which are automatically not counted in this number by our ticketing system, and subtracting dates would include these). I subsequently created the custom column [res_sla_business_duration] in Query as a duration using this formula: #duration(0,0,0,[taskslatable_business_duration]). I spot checked the output from this and found it accurate.

Then I tried to display the duration in a matrix and discovered (as many others have) that there's no built-in format for duration in DAX. So I tried a simple FORMAT on the AVERAGE, but it kept adding 30 days to the results and also my needs are slightly more complex, as I explain below. So I finally dipped my toe into DAX variables and ended up with the following expression based on help from many other posts:

Test Actual Duration =

VAR Average_Duration = AVERAGE('Resolution SLA'[res_sla_business_duration])

//Average of the duration column created in Power Query. Duration type columns return a decimal value representing days in DAX, and a decimal value representing days is required for the below modeling to work.

VAR actual_days_int = INT(Average_Duration) //returns whole days where 24 hours = 1 day

VAR actual_hours_dec = (Average_Duration - actual_days_int)*24 //subtracts the whole number from the value and multiplies only the decimal by 24 to return number of hours, where 24 hours = 1 day

VAR actual_hours_int = INT(actual_hours_dec) //returns whole hours remaining when 24 hours = 1 day

VAR actual_mins_round = ROUND((actual_hours_dec - actual_hours_int)*60, 0) //returns remaining minutes, rounded to the nearest whole minute

RETURN

FORMAT(actual_days_int, "00") & "." & FORMAT(actual_hours_int, "00") & ":" & FORMAT(actual_mins_round, "00") //Returns the calculated average as a formatted string

I then put this mesure in a matrix visual sliced on Date and Priority and compared the output against excel using the same data source - they match, so all is good.

Priority 1 |	00.01:59
Priority 2 |	00.08:47
Priority 3 |	00.12:49
Priority 4 |	00.07:48
Priority 5 |	01.10:29

Now, I actually need to model this data further, because for P3-P5 tickets we don't measure against 24 hour time, we measure only in 'Business Time' which is 8AM-5PM on working days (excludes holidays and weekends). This means that '1 Day' for these tickets is actually '9 hours' and this formula does not account for that. This is one of those things that are automatically excluded in my [taskslatable_business_duration] values, so showing the duration as 24 hours = 1 day is actually incorrect. Then I figured out the steps to convert from a decimal representing days in 24 hour time to a duration display where 1 day = 9 hours. I therefore wrote the following expression:

Test Business Duration =

VAR AvgBus = AVERAGE('Resolution SLA'[res_sla_business_duration])

VAR bus_days_dec = (AvgBus*24)/9 //returns a decimal value for number of days where 9 hours = 1 day

VAR bus_days_int = INT(bus_days_dec) //returns whole days where 9 hours = 1 business day

VAR bus_hours_dec = (bus_days_dec - bus_days_int)*9 //returns a decimal value for number of hours remaining when 9 hours = 1 day

VAR bus_hours_int = INT(bus_hours_dec) //returns whole hours remaining when 9 hours = 1 day

VAR bus_mins_round = ROUND((bus_hours_dec - bus_hours_int)*60, 0) //returns remaining minutes, rounded to the nearest whole minute

RETURN

FORMAT(bus_days_int, "00") & "." & FORMAT(bus_hours_int, "00") & ":" & FORMAT(bus_mins_round, "00") //Returns the calculated average as a formatted string

I again compared this to a manual calculation in Excel and found that the results matched.

Priority 1 |	00.01:59
Priority 2 |	00.08:47
Priority 3 |	01.03:49
Priority 4 |	00.07:48
Priority 5 |	03.07:29

Priority 1, 2, and 4 averages appear the same because they are not more than 9 hours total, but all 5 Priorities are being calculated here against business time (1 day = 9 hours) and if I slice the data so that the average is more than 9 hours, it turns it into a day. Great, as expected. However, P1 & P2 should NOT be displayed in business time, as these are actually 24 hour time. So I need to combine the two expressions to show the one calculation for P1 & P2 and the other for P3-P5. 

At first I tried combining all the variables into one DAX expression and returning an IF statement, but apparently you can't make an IF statement expression based on columns that aren't measures (tried to use the Priority). Stumped on how to do it in DAX, I figured this was actually a job for M Query, so I did the steps modelling days and hours from the original DAX expression (the two steps that are different depending on the priority) in Query custom columns using IF statements to control which way it did the calculation based on the priority:

= Table.AddColumn(#"Changed res_business_duration_calc Type to Number", "days", each if [priority] = "Priority 1" or [priority] = "Priority 2" then Number.IntegerDivide([taskslatable_business_duration], 86400) else Number.IntegerDivide([taskslatable_business_duration], 32400))

and 

= Table.AddColumn(#"Changed Type", "hours", each if [priority] = "Priority 1" or [priority] = "Priority 2" then (([taskslatable_business_duration]/86400)-Number.IntegerDivide([taskslatable_business_duration], 86400))*24 else (([taskslatable_business_duration]/32400)-Number.IntegerDivide([taskslatable_business_duration], 32400))*9)

I do not round either column, though they get converted into Number and Decimal types respectively, and used the original data field that is in seconds, figuring I would delete the [res_sla_business_duration] column. I spot checked the results from these formulas against the [res_sla_business_duration] field (seemed fine) and then wrote the following DAX expression for use in the visual:

Test Combined Duration =

VAR Average_Days = AVERAGE('Resolution SLA'[days])

VAR Average_Hours = AVERAGE('Resolution SLA'[hours])

VAR days_int = INT(Average_Days)

VAR hours_int = INT(Average_Hours)

VAR mins_round = ROUND((Average_Hours - hours_int)*60, 0) //returns remaining minutes, rounded to the nearest whole minute

RETURN

FORMAT(days_int, "00") & "." & FORMAT(hours_int, "00") & ":" & FORMAT(mins_round, "00")

The expected output of this, without additional slicing, should be the same as the one from the Test Business Duration since neither P1 nor P2 averages were over 9 hours. Instead I get this:

Priority 1 |	00.01:59
Priority 2 |	00.02:12
Priority 3 |	01.01:12
Priority 4 |	00.01:27
Priority 5 |	03.02:30

As you can see, all results except P1s are incorrect. I cannot figure out what I did wrong. I figure it has to be in the Query since the results are correct when I do the calculation in DAX (I manually calculated them in Excel based on the same dataset and compared), but I have stared and stared at them and can't figure out what's wrong. I can theorize that P1 values might still be correct because the volume of rows being averaged is lower (6 rows total) but I'm not sure that helps me find the issue?

I know we're supposed to post sample data, but I'm not sure how much help that will be since I can't share my actual source data, the source data is 20,000+ rows, and if the issue is only visible with larger volumes I can't really mock that up. But here's some information on my setup:

'Resolution SLA' table is not the fact table, and is related to the fact table by ticket number, which is distinct in both tables but I have a Many-to-1 relationship between them which allows filtering from the fact table to the 'Resolution SLA' table. The fact table is subsequently related to a DAX Date table and a DAX Priority table, which are set to filter the fact table. It's a Star Schema relationship model, with additional tables related to the fact table that are not relevant to this discussion. [taskslatable_business_duration] values are integers representing duration in seconds and can be zero. If zero, the value appears as a 0, not as null or empty.

Hoping someone can help me out. Thanks, Alex.

[lbendlin]

You get extra points for showing your work. However, as you have mentioned - no sample data = no help .  You will have to find a way to produce sample data that fully covers your issue (including the overflows etc. ) .

The issue itself seems solvable although it is definitely smelly - how can you guarantee that all your users are fully aware of the different computation for P1 and P2 versus P3 to P5 ? Seems to be ripe for misinterpretation.

[obera]

I was hoping someone could look at my formulas and point out an error. I don't even know how I could post sample data... I've always done it by embedding a table directly into the question, and there are too many rows for that. Security restrictions prevent me from uploading to a file sharing service.

As far as confusion over the different computations - the audience of this data is the folks who work the tickets. They are fully aware of the different ways it's calculated. I have no concerns in this area.