Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Average of minima

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.

 

IDDateOriginDestinationDuration
1372019-12-22 21:00:00.0000000Origin TownDestination Town - 23793
852019-12-22 19:00:00.0000000Origin TownDestination Town - 23810
9692019-12-24 19:00:00.0000000Origin TownDestination Town - 23822
9952019-12-24 20:00:00.0000000Origin TownDestination Town - 23824
10212019-12-24 21:00:00.0000000Origin TownDestination Town - 23859
1112019-12-22 20:00:00.0000000Origin TownDestination Town - 23877
592019-12-22 18:00:00.0000000Origin TownDestination Town - 23878
5272019-12-23 19:00:00.0000000Origin TownDestination Town - 23891
5532019-12-23 20:00:00.0000000Origin TownDestination Town - 23892
72019-12-22 16:00:00.0000000Origin TownDestination Town - 23932
332019-12-22 17:00:00.0000000Origin TownDestination Town - 23934
7872019-12-24 12:00:00.0000000Origin TownDestination Town - 23943
2672019-12-23 09:00:00.0000000Origin TownDestination Town - 23952
8132019-12-24 13:00:00.0000000Origin TownDestination Town - 23952
5012019-12-23 18:00:00.0000000Origin TownDestination Town - 23976
7352019-12-24 10:00:00.0000000Origin TownDestination Town - 23985
2412019-12-23 08:00:00.0000000Origin TownDestination Town - 23989
7612019-12-24 11:00:00.0000000Origin TownDestination Town - 23991
6572019-12-24 07:00:00.0000000Origin TownDestination Town - 24014
7092019-12-24 09:00:00.0000000Origin TownDestination Town - 24016
3192019-12-23 11:00:00.0000000Origin TownDestination Town - 24018
3712019-12-23 13:00:00.0000000Origin TownDestination Town - 24026
9432019-12-24 18:00:00.0000000Origin TownDestination Town - 24032
6832019-12-24 08:00:00.0000000Origin TownDestination Town - 24038
3452019-12-23 12:00:00.0000000Origin TownDestination Town - 24039
2152019-12-23 07:00:00.0000000Origin TownDestination Town - 24097
5792019-12-23 21:00:00.0000000Origin TownDestination Town - 24133
1632019-12-23 05:00:00.0000000Origin TownDestination Town - 24162
2932019-12-23 10:00:00.0000000Origin TownDestination Town - 24215
6052019-12-24 05:00:00.0000000Origin TownDestination Town - 24218
8392019-12-24 14:00:00.0000000Origin TownDestination Town - 24221
4492019-12-23 16:00:00.0000000Origin TownDestination Town - 24309
8652019-12-24 15:00:00.0000000Origin TownDestination Town - 24309
8912019-12-24 16:00:00.0000000Origin TownDestination Town - 24425
9172019-12-24 17:00:00.0000000Origin TownDestination Town - 24540
4232019-12-23 15:00:00.0000000Origin TownDestination Town - 24651
4752019-12-23 17:00:00.0000000Origin TownDestination Town - 24738
3972019-12-23 14:00:00.0000000Origin TownDestination Town - 24808
6312019-12-24 06:00:00.0000000Origin TownDestination Town - 24884
1892019-12-23 06:00:00.0000000Origin TownDestination Town - 24980


Best regards,


Martin.

1 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:

 

v-yuaj-msft_0-1611886706030.png

 

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.

View solution in original post

5 REPLIES 5
v-yuaj-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

 

Best Regards,

Yuna

Anonymous
Not applicable

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.

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:

 

v-yuaj-msft_0-1611886706030.png

 

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
Not applicable

Hi,

 

It works perfectly ! Thanks a lot !

 

Best regards,

 

Martin.

Anonymous
Not applicable

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.