L
Legacy 167309
Guest
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.
<tbody>
</tbody><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
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.
Sum of Duration (Mins) | Sum of Distance (m) | |
Week1 | ||
Person 1 | 85 | 5,200 |
Person 2 | 75 | 4,200 |
Person 3 | 144 | 9,300 |
Person 4 | 130 | 8,000 |
Person 5 | 252 | 14,000 |
Person 6 | 66 | 2,000 |
Person 7 | 91 | 3,500 |
Person 8 | 277 | 17,000 |
Person 9 | 252 | 17,000 |
Week2 | ||
Person 1 | 252 | 18,000 |
Person 2 | 253 | 23,000 |
Person 3 | 109 | 7,800 |
Person 4 | 109 | 6,500 |
Person 5 | 96 | 6,300 |
Person 6 | 271 | 9,700 |
Person 7 | 271 | 19,000 |
Person 8 | 109 | 8,000 |
Person 9 | 109 | 7,250 |
<tbody>
</tbody>
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