Find the maximum value of a group of data then average another data value based upon that max

[Legacy 167309]

Hi.

Completely new to this. Have been using powerpivot I guess at a basic level.

I have data whereby each week I have a list of people that work for certain durations of time each day over the course of the week. Over those durations they travel different distances. I want to compare the aggregate distances covered over a set of weeks. But to do that I want to identify the maximum duration from the week, then average the distance covered by any other person that has a duration within 10% of that maximum duration.


[TABLE="width: 379"]
<tbody>[TR]
[TD][/TD]
[TD]Sum of Duration (Mins)[/TD]
[TD]Sum of Distance (m)[/TD]
[/TR]
[TR]
[TD]Week1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Person 1[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]5,200[/TD]
[/TR]
[TR]
[TD="align: center"]Person 2[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]4,200[/TD]
[/TR]
[TR]
[TD="align: center"]Person 3[/TD]
[TD="align: center"]144[/TD]
[TD="align: center"]9,300[/TD]
[/TR]
[TR]
[TD="align: center"]Person 4[/TD]
[TD="align: center"]130[/TD]
[TD="align: center"]8,000[/TD]
[/TR]
[TR]
[TD="align: center"]Person 5[/TD]
[TD="align: center"]252[/TD]
[TD="align: center"]14,000[/TD]
[/TR]
[TR]
[TD="align: center"]Person 6[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]2,000[/TD]
[/TR]
[TR]
[TD="align: center"]Person 7[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]3,500[/TD]
[/TR]
[TR]
[TD="align: center"]Person 8[/TD]
[TD="align: center"]277[/TD]
[TD="align: center"]17,000[/TD]
[/TR]
[TR]
[TD="align: center"]Person 9[/TD]
[TD="align: center"]252[/TD]
[TD="align: center"]17,000[/TD]
[/TR]
[TR]
[TD]Week2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Person 1[/TD]
[TD="align: center"]252[/TD]
[TD="align: center"]18,000[/TD]
[/TR]
[TR]
[TD="align: center"]Person 2[/TD]
[TD="align: center"]253[/TD]
[TD="align: center"]23,000[/TD]
[/TR]
[TR]
[TD="align: center"]Person 3[/TD]
[TD="align: center"]109[/TD]
[TD="align: center"]7,800[/TD]
[/TR]
[TR]
[TD="align: center"]Person 4[/TD]
[TD="align: center"]109[/TD]
[TD="align: center"]6,500[/TD]
[/TR]
[TR]
[TD="align: center"]Person 5[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]6,300[/TD]
[/TR]
[TR]
[TD="align: center"]Person 6[/TD]
[TD="align: center"]271[/TD]
[TD="align: center"]9,700[/TD]
[/TR]
[TR]
[TD="align: center"]Person 7[/TD]
[TD="align: center"]271[/TD]
[TD="align: center"]19,000[/TD]
[/TR]
[TR]
[TD="align: center"]Person 8[/TD]
[TD="align: center"]109[/TD]
[TD="align: center"]8,000[/TD]
[/TR]
[TR]
[TD="align: center"]Person 9[/TD]
[TD="align: center"]109[/TD]
[TD="align: center"]7,250

[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
Essentially I do not want to include any individuals that have a low duration value as it would skew the data. So In week one I would only want the average of the distances covered of the highlighted players. They are within 10% of the maximum duration achieved (277mins) in that week. Which would be 16,000m.

For week two I would only use the highlighted to generate an average value of 17,425m as the maximum duration is 271 mins and the rest are within 10% of that maximum duration.



This will be used in a powerpivot so I am looking to use a calculated measure. This is only a set of dummy data but if someone would be happy to explain how I could maybe attempt this I'd very much appreciate it.

Thank you in advance

 

[tybaltlives]

You could use an array formula. ctrl-alt-enter after typing in the formula. seems to work.

Excel 2010
	A	B	C
1		Sum of Duration (Mins)	Sum of Distance (m)
10	Person 8	277	17000
11	Person 9	252	17000
12	Week2		17425
13	Person 1	252	18000
14	Person 2	253	23000
15	Person 3	109	7800
16	Person 4	109	6500
17	Person 5	96	6300
18	Person 6	271	9700
19	Person 7	271	19000
2	Week1		16000
20	Person 8	109	8000
21	Person 9	109	7250
3	Person 1	85	5200
4	Person 2	75	4200
5	Person 3	144	9300
6	Person 4	130	8000
7	Person 5	252	14000
8	Person 6	66	2000
9	Person 7	91	3500
Sheet2

Cell Formulas
Range	Formula
C2	{=AVERAGE(IF(B3:B11>0.9*MAX(B3:B11),C3:C11))}
C12	{=AVERAGE(IF(B13:B21>0.9*MAX(B13:B21),C13:C21))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tybaltlives]

sorry, my html converter is really weirded out. but the formula is correct if you put it in C2 and C12.

-R

 

[Legacy 167309]

Hi.

Thank you for this, however, I am trying to do it using powerpivot and a calculated measure.

Do you maybe have any suggestions?

K

 

[tybaltlives]

Sorry, I don't have powerpivot installed. Im working on that. Looks like an interesting add-on. However, it appears that you are just working with data tables. You could use the formula I supplied to create an additional table (which would could stay updated week by week) and then just access it through powerpivot? Just a thought.

 

[ImkeF]

This is how to do it in Power Query with M:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"duration", Int64.Type}, {"distance", type number}, {"week", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"week"}, {{"Max", each List.Max([duration])*0.9, type number}, {"Data", each _, type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"duration", "distance"}, {"duration", "distance"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each [duration] >= [Max]),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"week"}, {{"Average", each List.Average([distance]), type number}})
in
    #"Grouped Rows1"

simply load the result to the Data Model and it will be availabe for further use in Power Pivot


LinkToFile

 

[billszysz]

@Imke, I like your solution And here is a little shorter, based on yours ( but probably a little more complicated )

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"duration", Int64.Type}, {"distance", type number}, {"week", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"week"}, {{"Average", each List.Average(Table.SelectRows(_, (x) => x[duration] >= List.Max([duration])*0.9)[distance]), type number}})
in
    #"Grouped Rows"

sq

 

[ImkeF]

Hey Bill, always love to see your code

I often also prefer nesting of steps that belong together for better overview in my queries, but wonder which versions will scare M-newbies more:
Non-nested versions are easier to follow (and understand?) in the query editor but require longer code. Whereas nested versions often become more similar to Excel-formulas but there you are missing the formula evaluation-feature in M.

@All: What do you think about this?

 

[Legacy 167309]

@Imke and Bill.

Really appreciate this from both of you guys. Sorry for the delay but I have been away.

I get the logic of what you have written, @Imke, i'll use your file to take a look at it. I use powerquery for very basic results but I will try this out.

I'll let you know how I get on. Thank you very much again though. I appreciate your time.

Gogs