KPI: Average of top 10 performer

[motzfeldt01]

Hi,

I'm currently doing a benchmark analysis and I have to find the average GrossResult of the top 5 performers.

For example:

CompanyID	GrossResult
1	36,54
2	302,51
3	29,71
4	103,02
5	35,1
6	101,63
7	32,76
8	352,64
9	24,34
10	228,89

Here I'm interested in finding the number 217,738.

Does anybody have an idea how to do this in a KPI box?

[prashantsanchet]

Hi,

Please try to use below expression:

avg({<CompanyID = {'=Rank(Sum(GrossResult)) <=5'}>}GrossResult)

Hope this will help,

Regards,

Prashant

[motzfeldt01]

Hi,

It worked, thanks!

But what if I have a variable called vChosenIndustry, to identify a filtered industry. Where do I have to put that statement. Thus I don't get the overall top 5, but instead the top 5 of the filtered industry?

The detailed version:

I have the following code to calculate the average GrossResult in a particular industry

avg({<[CompanyID]=, [CompanyName]=, Industry={$(vChosenIndustry)}>}[GrossResult])

But as you know, now I only want the average for top 5.

Kind regards

Andreas

[shraddha_g]

Try

avg({<CompanyID = {'=Rank(Sum({<IndustryField = {"$(=vChosenIndustry)"}>}GrossResult)) <=5'}>}GrossResult)

[motzfeldt01]

It gives me 193,7 but should give me 217,7.

193,7 was also the number it gave me before, which doesn't make sense after we added "{<IndustryField = {"$(=vChosenIndustry)"}>}" .

[shraddha_g]

Please check what value the variable is giving

$(=vChosenIndustry)

or Simply vChosenIndustry

[motzfeldt01]

Oh, I forgot. It's because I have to specify from what reporting year we have to use the Grossresult. Otherwise it will sum all the years. So, I tried to add that perspective to your code: avg({} [GrossResult]) But it doesn't show any value. Can you spot the what I'm doing wrong?

[shraddha_g]

can you please show what you have done

[motzfeldt01]

Sorry, here is the code:

avg({<[CompanyID] =

{'=Rank(Sum({<Industry = {"$(=vChosenIndustry)"},

[XBRL Reporting year] = {"$(=vXBRLThisYear)"}>}[GrossResult])) <=5'}>}

[GrossResult])