cancel
Showing results 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

Frequent Visitor

## Get Average earliest hour per delivery_round_id for each postal code

I have a table called filtered_POR050 with multiple columns containing parcel delivery information (postal code, hour, day, delivery round id) from which I am able to create the following matrix:

From what you can see:

1. GroupeCP is a column with postal codes
2. 520, 521, 522.. is a column with delivery round id called ID TOURNEE
3. lundi 11 septembre 2023.. is a column with delivery date called JOUR LIV
4. Max_Heure LIV is a measure that calculates the latest delivery hour from column Label_HEURE LIV
5. Min_Heure LIV is a measure that calculates the earliest delivery hour from column Label_HEURE LIV

Here are my formulas:

```Max_Heure LIV = CALCULATE(MAX('filtered_POR050'[Label_HEURE LIV]))

Min_Heure LIV = CALCULATE(MIN('filtered_POR050'[Label_HEURE LIV]))```

My matrix is built as follows:

I am satisfied with the matrix so far. It calculates well the max values per day and per ID TOURNEE for each postal code. However, The values in bold, are not what I want, I'd like to have an additional measure that takes the results of every Max_Heure LIV per day (for example, the non bold values you see for "520") and calculates their average value per each ID TOURNEE. So, each ID TOURNEE will have an average Max_Heure LIV value (not the 13:31, which is the max, not the average).

As a solution, I've tried:

`AVG = AVERAGEX(Values(filtered_POR050[Label_HEURE LIV]), [Max_Heure LIV])`

But this gives me a 0 everywhere. What should I do?

-------- UPDATE ---------------

1 ACCEPTED SOLUTION
Frequent Visitor

Edited - I think I had misread your question. Below should hopefully work! It takes an average of all max time. The same would work for the MIN time, just swap out where you see MAX with MIN.

`AVG_Max = FORMAT(averagex(SUMMARIZE(filtered_POR050, filtered_POR050[GroupeCP],filtered_POR050[ID TOURNEE], filtered_POR050[Date], "MAX", [Max_Heure LIV]), [MAX]),"HH:mm")`

2 REPLIES 2
Frequent Visitor

Edited - I think I had misread your question. Below should hopefully work! It takes an average of all max time. The same would work for the MIN time, just swap out where you see MAX with MIN.

`AVG_Max = FORMAT(averagex(SUMMARIZE(filtered_POR050, filtered_POR050[GroupeCP],filtered_POR050[ID TOURNEE], filtered_POR050[Date], "MAX", [Max_Heure LIV]), [MAX]),"HH:mm")`

Frequent Visitor

Thank you @RainAndSnow , this did work!
I've done the same for the MIN and also, I've made the difference between the Max and the Min to get the Average time passed per day per ID at a postal code, as follows:

AVG_Time = FORMAT(([AVG_Max] - [AVG_Min]), "hh:mm")

Now, this would be accurate if there were no pauses between delivery hours:Let's say at some point at Monday, between 13:00 and 14:00 there might be a gap of 1 hour due to a pause that the driver has taken. How can I account for that for the final value?

Is there a way to account for any gaps of more than 30min in a chosen day, per ID at a postal code, and substract the sum of such gaps from the final AVG_Time?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.