Ave Function

[Anonymous]

Hello

I have this table:  I want to know the average quantity of top two customers by item number, how I could do this?

item number	Q	Customer
100	22	A
101	15	B
102	23	C
103	19	D
104	16	A
105	10	B
106	25	C
107	16	D
108	16	A
109	22	B
110	20	C
100	12	D
101	20	A
102	24	B
103	21	C
104	22	D
105	10	A
106	14	B
107	18	C
108	15	D
109	25	A
110	14	B
100	23	C
101	21	D
102	10	A
103	14	B
104	19	C
105	19	D
106	12	A
107	22	B
108	18	C
109	20	D
110	10	A
100	16	B
101	10	C
102	24	D
103	15	A
104	23	B
105	21	C
106	22	D
107	25	A
108	10	B
109	16	C
110	12	D
100	23	A
101	11	B
102	10	C
103	11	D
104	15	A
105	12	B
106	15	C
107	23	D
108	18	A
109	24	B
110	22	C
100	10	D
101	11	A
102	12	B
103	16	C
104	21	D
105	17	A
106	21	B
107	25	C
108	24	D
109	14	A
110	20	B
100	13	C
101	15	D
102	22	A
103	15	B
104	16	C
105	24	D
106	17	A
107	22	B
108	25	C
109	14	D
110	14	A

for example the answer for item number 100 and 101 should be:

CUSTOMER	Item number	q	Ave Top 2 customer
A	100	45	23
A	101	31	20.5

[Anil_Babu_Samineni]

May be this?

Avg({<Customer = {"=Rank(Sum(Q))<=2"}>} Q)

[Anonymous]

Hi Anil Babu

It is somehow correct, but the answer for item number  101 should be 33.5, actually the sum of average. How to do this? For item number 101: (36+31)/2=33.5

[Anonymous]

How to sum this  Avg({<Customer = {"=Rank(Sum(Q))<=2"}>} Q) expression for each item number?

[Anonymous]

Sorry, the right answer is:

CUSTOMER	Item number	q	Ave Top 2 customer
A	100	45	40.5
A	101	31	33.5

[Anil_Babu_Samineni]

Perhaps this?

Dimensions are

Customer - =If(Aggr(Rank(Sum(Q)), Customer)<=2, Customer)

[Item Number] - =If(Aggr(Rank(Sum(Q)), [item number])<=2, [item number])

Expressions are

Avg(TOTAL <[item number]> Aggr(NODISTINCT Sum(TOTAL <Customer,[item number]> Q), Customer,[item number]))

OR

Avg(TOTAL <[item number]> Aggr(Sum(TOTAL <Customer,[item number]> Q), Customer,[item number]))

[sunny_talwar]

May be this

Avg(Aggr(If(Rank(Sum(Q)) <= 2, Sum(Q)), [item number], Customer))

[Anonymous]

Hi Sunny

It works.

Thank you so much

[prma7799]

Please close this thread as helpful or right answer.