Top 5 Values from a sum

[lucas3]

Hi all,

I would like to print on a Text & Image "Chart" a top 5 values of a sum over some companies balances. I need to get both my top 5 companies names and the matching balances I want to display

Here is my expression for my top 1 company, this one works fine:

=Only(If(Aggr(Rank(Sum({$<[Account Number]={"467*"}>} Solde )),[Company Name])=1,[Company Name]))

But then, I don't know how to get the balance for a top company. The input to get the balances I want through ANY company, would be this one (it works):

= Sum( {$ <[Company Name] = {"467*"} >} Balance)

But this gets me as much values as i have companies, it does not work for my Text and Image chart... For example, I would like to print it just for my top 3 company according to the above expression. How can I do that ?

Thank you 

[MayilVahanan]

Hi 

Try like below

=Concat(Distinct (Aggr(If(Rank( Sum( {$ <[Company Name] = {"467*"} >} Balance)) <= 3, [Company Name]& ' ('& Sum( {$ <[Company Name] = {"467*"} >} Balance)&')') , [Company Name])), chr(13))

[lucas3 · Accepted solution]

Thank you ! You just confused Account Number and Company Name, but when I replaced it, it worked perfectly well. Here is the formula I used:

=Concat(Distinct (Aggr(If(Rank( Sum( {$ <[Account Number] = {"467*"} >} Balance)) <= 3, [Company Name]& ' ('& Sum( {$ <[Account Number] = {"467*"} >} Balance)&')') , [Company Name])), chr(13))