Average Time by Distinct Group

[Anonymous]

Hi,

Hopefully this is in the right place, and if anyone can help it would be really appreciated.

I have data similar to the below table.   I want to find the average time taken per distinct order and test group.

Order	Test Group	Order & Test Group	Code	Time Taken
1	A	1A	1	5
1	A	1A	2	5
1	A	1A	3	5
1	B	1B	1	15
2	A	2A	1	10
2	B	2B	1	10
3	A	3A	1	20

So in this example the code columns means that I am getting 3 counts of '1A', but I only want 1, so I want my average to be

(5+15+10+10+20)/5=12, not (5+5+5+15+10+10+20)/7=10.  I basically want to ignore the duplicate 1A Order& Test Groups.

Do you know if this is possible.

Let me know if you would need any extra information.

Thank you,

Neil

[sunny_talwar · Accepted solution]

May be this

Avg(Aggr(Only([Time Taken]), Order, [Test Group]))

[effinty2112]

Hi Neil,

try:

=Sum(Aggr(Sum([Time Taken]),Order,[Test Group]))

/

Sum(Aggr(Count([Time Taken]),Order,[Test Group]))

In working out averages I always like to do the simple thing and that's to work out a grand total then divide by the number of instances we're taking our average over.

I think (for me at least) it's very easy to go astray when you wrap an aggr with an avg().

cheers

Andrew

[sunny_talwar]

What issues do you foresee using Avg(Aggr())

[effinty2112]

Hi Sunny,

Apologies to you and Neil. I didn't read the second half of Neil's question properly, so Neil please disregard my half baked reply to you. Regarding my issues with Avg(Aggr()) I guess I'm just more comfortable writing it out the way I do and find it easier to read.

Cheers

Andrew

[sunny_talwar]

Hey Andrew -

Not sure what you are apologizing for. You don't have to apologize brother. I guess it might be easier to read, but if you can get the result using a single Aggr(), wouldn't you want to do that instead of getting the same result using 2 Aggr()? (Unless you have a small dataset)

[effinty2112]

Hi Sunny,

My apologies were really because I didn't read the question properly and the answer I gave was wrong. It gave 12 10 instead of 10 12 as the result.

Following my logic the correct (though cumbersome) expression would be

Sum(Aggr(only([Time Taken]),Order,[Test Group]))

/

Sum(Aggr(Count(DISTINCT [Time Taken]),Order,[Test Group]))

Anyway you are right - especially with large volumes. I think I've got a hangup I need to lose, I must have had a bad experience with avg(aggr()) in the past .

Cheers

Andrew

[effinty2112]

Hi Sunny,

It may be a half remembered reading of this that causes by aversion to avg(aggr()).

Average – Which average?

Cheers

Andrew

[Anonymous]

Thanks for your help guys they both work really well.  Much appreciated.

[MarcoWedel]

Hi,

another way to get this average might be:

hope this helps

regards

Marco