Top 5 + Bottom 5 In The Same Chart

[intapiuser]

Analytical Need 

You want to display a chart/table with the Top X and Bottom X, all in the same widget 

Modeling Challenge

Sisense allows setting ranking filters based on a measure, but this is only for Top OR Bottom.

Solution

Widget Filter:

1. Create your widget, with the value you want to rank by (for ex. sum( [Visits] ) ).
2. Create the rank filter measure
3. Start with the RANK function to rank the values DESC
4. Wrap the RANK function with an IF statement, saying IF (Rank <= X , 1, 0).  This will return a value of 1 if the member is in the top X
5. Repeat this, but switch the RANK to a DESC in order to get a value of 1 if the member is in the bottom X
6. Add these two IF statements together, which will give you a value of 1 for Top X members and the Bottom X members
7. Create a widget filter from your formula, that filters where the value = 1

(Optional) Include the ranking:

1. If you just try to add your RANK function to the widget, it won't work.  This is because the values in the widget are sliced by the widget filter down to just the Top/Bottom X, and then the RANK is calculated (which means the RANK will just show values of 1-10).
2. Create the true rank formula
3. Start with the RANK function to rank the values DESC, and wrap it within an IF statement
4. If the rank <= X, then just display the rank
5. else, count the unique values of the dimension and subtract the rank ASC + 1

Example Dashboard: Download