This is something we get asked a lot – how do I find the Top 10 Products, Customers, Suppliers by revenue or volume so that whenever I open the report, they change automatically when new data refreshes the model? This tip works whatever Dimensions and Summary Values you are working with.
The usual mistake people make is to dive by the dimension they want to work with, sort down by value and then highlight the first 10 rows and focus. This is fine for now, but what about tomorrow, next week or next month? The values might have changed, but the highlighted rows will still contain the same Products, Customers or Suppliers etc. This is because a straightforward “Focus”, or “Group” is static as far as the dimension members are concerned. You will potentially have missed the latest Top 10, or if you go back in time, earlier Top 10s.
So, follow these instructions and you will end up with a Top 10 Report that works dynamically, whenever you open it and whatever data you choose. It can also be used in conjunction with Time Series and other Quickviews for further filtering.
1. Start by diving on the Dimension you wish to see in the report.
2. Select the value column you want to work with and click the “Sort Down” icon.
3. Next to the sort down icon is the “Find” icon. Click this and check the boxes as below, entering 10 (or another integer that you would like to use, say, Top 5 or Top 20).
4. The resulting table should now only display the Top 10 rows.
5. Save this as a “marker to file” and call it a suitable name.
6. If you prefer, format as a report by clicking the report icon and giving it a title. Then save again.
7. Here is one style of report – you can choose any colour, border, background, font style and size.
If you have any questions about this post, or any other queries, please just Ask Anthony though the form below.