Display average TOP 5 for all rows

[dafnis14]

Hi,

I have a table where I use an expression taken from another post

Ranking max top 5

However, I need to show the average of the TOP 5 for any branch, even if the branch

is not in the top 5. Is it possible?

Attached is a very simplified model of my requirement, ere only the top 5 branched are displayed.

Thanks!

[kaushiknsolanki · Accepted solution]

Try this expression.

avg({<Branch = {"=Rank(TOTAL avg(Score),0)<=5"}>}Total<Year> Score)

Hope this is what you want.

Regards,

Kaushik Solanki

[sunny_talwar]

Are you looking to get this?

May be this:

Avg(TOTAL <Year> {<Branch = {"=Rank(TOTAL Avg(Score), 0) <= 5"}>} Score) + Avg({1} 0)

or

Avg(TOTAL {<Branch = {"=Rank(TOTAL Avg(Score), 0) <= 5"}>} Score) + Avg({1} 0)

[dafnis14]

Awesome Kaushik!

Thank you very much for your help!

[dafnis14]

Hi Sunny,

I saw your solution in another post, and I admit it was too sophisticated for me to understand. (Avg{1} 0).. even with your explanations.

But I will check it more seriously.

The sample requirement might not make sense, but in reality, I would need to show one row per  sub-category, and each sub-category is linked to a different upper category.  

Thanks for your kind help 🙂