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