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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
eyewee
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:
4Xd8n.png


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:
D39sK.png
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
RainAndSnow
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")

RainAndSnow_1-1704234006939.png

 

 

View solution in original post

2 REPLIES 2
RainAndSnow
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")

RainAndSnow_1-1704234006939.png

 

 

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?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.