Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
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 ---------------
Solved! Go to Solution.
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")
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")
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?
User | Count |
---|---|
123 | |
76 | |
63 | |
50 | |
50 |
User | Count |
---|---|
175 | |
125 | |
60 | |
60 | |
58 |