Excel / DAX Averaging Issue

0
votes

Thank you for taking the time to look at my question.

DAX doesn't allow for durational values, so in order to work out an average value for a list of durational values, I've split the durations into their component 'Hour', 'Minute' and 'Second' values. Each of these have then been averaged and then concatenated as something of a workaround.

Here's the confusion:

In Excel, when I divide the duration-in-seconds value by 86400 (and consequently format in [h]:mm:ss), the individual values tally up precisely (e.g. 10:49:12 is derived from the duration-in-seconds value and the individual 'Hour', 'Minute' and 'Second' values match - 10, 49 and 12 respectively) - yet when I average the duration-in-seconds column, I receive a different value to that of averaging the component time values and concatenating them (separated by a colon).

I'm sure that this will be something really simple, so I'm hoping that one of the great minds on here will be able to put me out of my misery and provide a concise explanation...it's all too much for my tiny mind as it is; sleep deprivation notwithstanding!

Thank you very much in advance,

Rob

1
excelconcatenationaveragepowerbidax
Do you have a minimum reproducible sample / data set? - Foxan Ng
@FoxanNg Thanks for responding and apologies for the delay in my own reply. I think the issue is more inherent to the calculation engine rather than a specific dataset - I was able to replicate the issue by populating a column in Excel (using RANDBETWEEN 0-150000 to represent time-in-seconds) and doing the same calculations. Edit: Note that I pasted the random numbers as values so that they were not a dynamic value. :) - HendrixAndChill
It's not down to the calculation engine - its down to the approach you are taking. If you take two times 10:00:01 and 09:59:59 and average them, the result should be 10:00:00. However if you break it down into the component parts and average those, you get 9.5, 29.5 and 30. Depending how you concatenate these, you may get 10:30:30. The minutes and seconds are meaningless when separated from the hours. You can only average the times when you consider the time as a whole. - Gordon K
@GordonK Hi Gordon, thank you very much for responding. I didn't mean the calculation engine was at fault in the sense that it was malfunctioning - I think I innately suspected something along the lines of your explanation, but was unsure exactly what it was. You've confirmed exactly what I thought might be the issue - although I only knew that something wasn't quite right! Thanks again for the clarification. :) - HendrixAndChill

1 Answers

1
votes

I have no issue replicating the calculations in Power BI using DAX. You can check if the DAX are the same as yours.

Table with 100 rows of random data:

table

DurationInSeconds:

DurationInSeconds = RANDBETWEEN(0, 150000)

Hour:

Hour = QUOTIENT('Time'[DurationInSeconds], 3600)

Minute:

Minute = MOD(QUOTIENT('Time'[DurationInSeconds], 60), 60)

Second:

Second = MOD('Time'[DurationInSeconds], 60)

AverageByDurationInSeconds:

AverageByDurationInSeconds = AVERAGE('Time'[DurationInSeconds])

AverageByHourMinuteSeconds:

AverageByHourMinuteSecond = AVERAGE('Time'[Hour]) & ":" & AVERAGE('Time'[Minute]) & ":" & AVERAGE('Time'[Second])

Results:

results

Of course the measure AverageByHourMinuteSeconds won't make much sense because decimal places must be retained for accuracy, but the calculation is still valid. (21.9 * 3600 + 29.73 * 60 + 29.77 = 80653.57)