One of the frequent questions I come across is I want to view the top 5, however I want everything else that is not in the top 5 to be placed in 1 bucket called others. I ran into the same question on the BOB board today and since it was simple enough here comes the solution.
Getting the rank within WEBI is quite simple using the RANK function. The syntax for the function is RANK(measure name; dimension name; top|bottom). For example if I wanted to find out the rank by sales I could define RANK([Sales Revenue], [City], top).
In order to make my life easier I have defined variable called Rank as shown below.
I can use that in a report filter to get only the top 3 regions by Sales Revenue.
Now to display all other regions I will use the WEBI function NOFILTER. This will return the total sales across all the regions irrespective of the filter. Using the formula below I can get the sales for all the regions that are not in top 3.
By placing this in the footer of the table I can get the report to show the sales for others.
.svg)
.svg)