Creating average by unique countries

[gabormajdan]

Hi All,

I want to create the average score of an HQ, based on its top 3 highest scoring subsidiaries. There is one condition: the top 3 has to be composed of unique countries, i.e. the average cannot contain more than 1 subsidiary based in the same country. If a such a case occurs, we will take the highest score into account. See the example below.

Here is the source data:

HQ	Subsidiary	Country	Score
HQ_1	Company_A	FRA	6
HQ_1	Company_B	ITA	5
HQ_1	Company_C	FRA	4
HQ_1	Company_D	GER	2
HQ_2	Company_E	ESP	7
HQ_2	Company_F	ITA	4
HQ_2	Company_G	GER	3
HQ_2	Company_H	ESP	2

The resulting table would look like this:

Rank	HQ	Average Score
1.	HQ_2	4.67
2.	HQ_1	4.33

Notice that for HQ_1 we had to take the average of companies A, B and D, because A and C are both from France and A has the highest score. Therefore C was omitted. H was omitted because it is not in the top 3. (note: we don't have to deal with the dilemma of an HQ having subsidiaries from only 1 country)

I have the following piece of code, that does the job of picking the top 3 of any HQ, but I cannot get it to exclude a duplicate country.

avg(

    if(

        aggr(num(rank(sum(Score),1)), HQ, Subsidiary) < 4,

        sum(Score)

    )

)

Thanks in advance for your help!

Gabor

[Gysbert_Wassenaar · Accepted solution]

Try this: avg(aggr(if(rank(max(Score))<4,max(Score)),Country,HQ))

[swuehl]

I would use

=avg(aggr(if(rank(max(Score))<4,max(Score)),HQ, Country))

[gabormajdan]

Thanks! I had to tweak it a bit myself, because I gave a simplified version of the problem, but overall this was the right solution (in reality the Score is a lengthy calculation so I had to embed another aggr in the max, over Country, HQ and Company fields).

Something like this:

avg(aggr(if(rank(max(aggr(Score,Country,HQ,Company)),<4,max(aggr(Score,Country,HQ,Company))),Country,HQ))