cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

 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.

1 ACCEPTED SOLUTION
Community Support

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] ))RETURNAVERAGEX ( 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.

5 REPLIES 5
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.

Community Support

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] ))RETURNAVERAGEX ( 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
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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors