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.

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.

Top_Rank.jpg

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.

Top_Vlookup.jpg

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.

Same_Value_Error.jpg

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

Correction.jpg

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

With_Correction.jpg

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

Bottom.jpg

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

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Anilkumar Cheetirala

    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)

    (0) 
    1. Abhijit Ingale 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

      (0) 
  2. Ranendra Parida

    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

    (0) 
  3. Nishant Nair

    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

    (0) 

Leave a Reply