Average of minima

[Anonymous]

Hello there,

I need help on a measure I want to create.

In my data, I have durations by date, origin and destination, with a few durations for each.
What I want to create is a measure that would give the average duration value between the minimum values of each selection (with date, origin, and destination filtered).

At first I tried this formula (the duration is in seconds, so I need to format it) :
TIME(0,0,AVERAGE(CALCULATE(MIN('File1'[Duration]),'File1'[Date],'File1'[Origin],'File1'[Destination])))

But I got an error message with : "AVERAGE function only accepts column reference as an Argument".

So I tried splitting the calculation into two measures, and I found this post on the subject :
@https://community.powerbi.com/t5/Desktop/Calculate-average-of-minimum-by-group/m-p/1451886#M608275

It proved really useful, but only the first part works for me :
Min Date = CALCULATE(MIN('File1'[Duration]),ALLEXCEPT('File1','File1'[Date],'File1'[Origin],'File1'[Destination]))

The second part : Avg Min = AVERAGEX('File1',[Min Date]), gives the same result as a simple Avg = AVERAGE('File1'[Duration])

So I tried creating yet another measure using the first one :
Min Date 2 = CALCULATE('File1'[Min Date],'File1'[Date],'File1'[Origin],'File1'[Destination])

When I use this new measure in Avg Min, it only works if I use one dimension in Min Date 2 (the result is more accurate, but still wrong), and Avg Min doesn't wotk at all if I try putting the three dimensions in Min Date 2. I also tried replacing the Origin-Destination names by IDs, but the value is still wrong.

So it seems the problem comes from using three dimensions, instead of one like in the post.

Can you help me ? I post a data extract as well.

ID	Date	Origin	Destination	Duration
137	2019-12-22 21:00:00.0000000	Origin Town	Destination Town - 2	3793
85	2019-12-22 19:00:00.0000000	Origin Town	Destination Town - 2	3810
969	2019-12-24 19:00:00.0000000	Origin Town	Destination Town - 2	3822
995	2019-12-24 20:00:00.0000000	Origin Town	Destination Town - 2	3824
1021	2019-12-24 21:00:00.0000000	Origin Town	Destination Town - 2	3859
111	2019-12-22 20:00:00.0000000	Origin Town	Destination Town - 2	3877
59	2019-12-22 18:00:00.0000000	Origin Town	Destination Town - 2	3878
527	2019-12-23 19:00:00.0000000	Origin Town	Destination Town - 2	3891
553	2019-12-23 20:00:00.0000000	Origin Town	Destination Town - 2	3892
7	2019-12-22 16:00:00.0000000	Origin Town	Destination Town - 2	3932
33	2019-12-22 17:00:00.0000000	Origin Town	Destination Town - 2	3934
787	2019-12-24 12:00:00.0000000	Origin Town	Destination Town - 2	3943
267	2019-12-23 09:00:00.0000000	Origin Town	Destination Town - 2	3952
813	2019-12-24 13:00:00.0000000	Origin Town	Destination Town - 2	3952
501	2019-12-23 18:00:00.0000000	Origin Town	Destination Town - 2	3976
735	2019-12-24 10:00:00.0000000	Origin Town	Destination Town - 2	3985
241	2019-12-23 08:00:00.0000000	Origin Town	Destination Town - 2	3989
761	2019-12-24 11:00:00.0000000	Origin Town	Destination Town - 2	3991
657	2019-12-24 07:00:00.0000000	Origin Town	Destination Town - 2	4014
709	2019-12-24 09:00:00.0000000	Origin Town	Destination Town - 2	4016
319	2019-12-23 11:00:00.0000000	Origin Town	Destination Town - 2	4018
371	2019-12-23 13:00:00.0000000	Origin Town	Destination Town - 2	4026
943	2019-12-24 18:00:00.0000000	Origin Town	Destination Town - 2	4032
683	2019-12-24 08:00:00.0000000	Origin Town	Destination Town - 2	4038
345	2019-12-23 12:00:00.0000000	Origin Town	Destination Town - 2	4039
215	2019-12-23 07:00:00.0000000	Origin Town	Destination Town - 2	4097
579	2019-12-23 21:00:00.0000000	Origin Town	Destination Town - 2	4133
163	2019-12-23 05:00:00.0000000	Origin Town	Destination Town - 2	4162
293	2019-12-23 10:00:00.0000000	Origin Town	Destination Town - 2	4215
605	2019-12-24 05:00:00.0000000	Origin Town	Destination Town - 2	4218
839	2019-12-24 14:00:00.0000000	Origin Town	Destination Town - 2	4221
449	2019-12-23 16:00:00.0000000	Origin Town	Destination Town - 2	4309
865	2019-12-24 15:00:00.0000000	Origin Town	Destination Town - 2	4309
891	2019-12-24 16:00:00.0000000	Origin Town	Destination Town - 2	4425
917	2019-12-24 17:00:00.0000000	Origin Town	Destination Town - 2	4540
423	2019-12-23 15:00:00.0000000	Origin Town	Destination Town - 2	4651
475	2019-12-23 17:00:00.0000000	Origin Town	Destination Town - 2	4738
397	2019-12-23 14:00:00.0000000	Origin Town	Destination Town - 2	4808
631	2019-12-24 06:00:00.0000000	Origin Town	Destination Town - 2	4884
189	2019-12-23 06:00:00.0000000	Origin Town	Destination Town - 2	4980

Best regards,

Martin.

[v-yuaj-msft]

Hi @Anonymous ,

Could you please share a screenshot of the expected result of the sample data given above?

Best Regards,

Yuna

[Anonymous]

Hi,

Thank you for the help !

Here, the minimum values are :

3793 for the 22/12

3891 for the 23/12

3822 for the 24/12

(I just realised, when you read it, you have to ignore the "2" that is the end of "Destination Town - 2", a copy-paste in Excel gives the right results).

So the expected result would be the average between these three values : 3 835,33

For now, with the current formula, (Min Date and Avg Min, as in the mentioned post) I obtain 4,13K.

Best regards,

Martin.

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a measure as follows.

ave_min_duration =
VAR x1 =
SUMMARIZE (
'Table',
'Table'[Date].[Date],
"Min_value", MIN ( 'Table'[Duration] )
)
RETURN
AVERAGEX ( x1, [Min_value] )

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

It works perfectly ! Thanks a lot !

Best regards,

Martin.

[Anonymous]

Hello there (again),

I add a precision, as I realised I missed something regarding the three dimensions :

A user is supposed to systematically select one origin and one dimension, but can keep various dates.

So the calculation is for an average of the minimum values per date, after we have one origin and one destination selected.

Best regards,

Martin.