topic Re: Top 5 Values from a sum in Reporting Service & Alerting

Top 5 Values from a sum

lucas3 — Fri, 15 Nov 2024 22:11:07 GMT

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

Re: Top 5 Values from a sum

MayilVahanan — Thu, 19 May 2022 09:32:34 GMT

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))

Re: Top 5 Values from a sum

lucas3 — Fri, 20 May 2022 12:14:13 GMT

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))