Skip to Content
Author's profile photo Abhijit Ingale

Dashboards Trick – Finding Top or Bottom N Items based on a Measure


One of the very common requirements on the dashboards is to show Top n or Bottom n items based on certain key figures. It may not be always feasible and performance wise advisable to get these from database to show on the dashboard. There are some excel tricks to derive this in excel but those do not work in all scenarios, specifically when there are duplicate measure values. Please see below a simple trick using excel formulas that will help in getting the Top/Bottom n items based on measure that works in all scenarios.


Microsoft Excel 2007, SAP Dashboards 4.1 SP2


1. Let us assume, I have State, Revenue and Quantity data and I need to get the Top and Bottom 5 States by Revenue.

Original data.jpg

2. Add another column Top with the RANK formula i.e. RANK(H2,$H$2:$H$10,0). Please see the snapshot below. It will rank all the records by Revenue in Descending order. Since all the Revenue numbers are unique, it has ranked each record properly.


3. Now I need to extract Top 5 States from this data set. For this, VLOOKUP function can be used. I hard coded the Ranks from 1 to 5 in one column and then used that in VLOOKUP to extract records from the above data set.


4. In a scenario wherein same Revneue number is present for 2 different states in the data set, RANK formula ranks those 2 states equally and then VLOOKUP fails. Please see the snapshot below.


5. To avoid this, a correction factor can be added to ranking thus allowing to rank each state serially even though they have same Revenue number. Please see the snapshot below with the correction factor formula. It returns 1 only for one of the records having same Revenue number. For all the records with distinct Revenue number, it returns 0. Final Rank can be calculated by adding Correction and Top columns (E+F).


6. VLOOKUP formula need to be modified to take into consideration Final Rank. This fixes the error and provides the expected results.


7. By follwing the same steps, BOTTOM n values can be derived with slightly modified RANK formula e.g. RANK(H2,$H$2:$H$10,1).


I have used these formulas in SAP Dashboards 4.1 SP2. It worked fine without any issue.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member


      Thanks for sharing useful info.

      Here I have a ques,

      How did you get 'Chio' Correction value as '0' - When I tried I am getting 1 for both Chio and Newyork. (USING EXCEL 2013)

      Author's profile photo Abhijit Ingale
      Abhijit Ingale
      Blog Post Author

      Hi Anil,

      I hope you have used formula without fixing up the range as shown below -


      With this the H2:H10 keep changing and hence duplicates are flagged only once. The formula from the last correction cell in the example above is as shown below -


      I hope this helps.



      Author's profile photo Former Member
      Former Member

      Hi Abhijit,

      Thanks for correcting my formula.Its working.

      Author's profile photo Former Member
      Former Member

      I am facing some problem like not getting combination chart 2 - column chart in Xcelsius 2008. Can somebody please help me regarding this.....

      Thanks and Regards


      Author's profile photo Former Member
      Former Member

      Hi Abhijit,

      This was very useful but there is a problem which I am facing with the duplicate rankings.
      I tried to fix the range also but I am not getting to the solution.

      Thanks and Regards


      Author's profile photo Former Member
      Former Member

      Hi Abhijit,

      Thanks for sharing.



      Author's profile photo Former Member
      Former Member

      Very useful document.. Thanks Abhijit

      Author's profile photo santhana lakshmi
      santhana lakshmi

      Nice.crystal clear explanation.

      Author's profile photo TEAM ABAP

      Thanks a lot Abhijit. This saved my day.