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

Purpose:

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.

Technology:

Microsoft Excel 2007, SAP Dashboards 4.1 SP2

Scenario:

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

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.

Hi,

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)

Abhijit Ingale
Blog Post Author

Hi Anil,

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

COUNT(H2:H10)+1-RANK(H2,H2:H10,0)-RANK(H2,H2:H10,1)

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 -

COUNT(H10:H18)+1-RANK(H10,H10:H18,0)-RANK(H10,H10:H18,1)

I hope this helps.

Regards,

Abhijit

Hi Abhijit,

Thanks for correcting my formula.Its working.

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

Ranendra

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

Nishant

Hi Abhijit,

Thanks for sharing.

Regards,

Hitesh

Very useful document.. Thanks Abhijit

Nice.crystal clear explanation.

Thanks a lot Abhijit. This saved my day.