8 Replies Latest reply: Aug 29, 2018 6:04 AM by Andreas Jensen

KPI: Average of top 10 performer

Hi,

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

For example:

CompanyIDGrossResult
136,54
2302,51
329,71
4103,02
535,1
6101,63
732,76
8352,64
924,34
10228,89

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

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

• Re: KPI: Average of top 10 performer

Hi,

Please try to use below expression:

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

Hope this will help,

Regards,

Prashant

• Re: KPI: Average of top 10 performer

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

• Re: KPI: Average of top 10 performer

Try

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

• Re: KPI: Average of top 10 performer

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)"}>}" .

• Re: KPI: Average of top 10 performer

Please check what value the variable is giving

\$(=vChosenIndustry)

or Simply vChosenIndustry

• Re: KPI: Average of top 10 performer

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?

• Re: KPI: Average of top 10 performer

can you please show what you have done

• Re: KPI: Average of top 10 performer

Sorry, here is the code:

avg({<[CompanyID] =

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

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

[GrossResult])