Scenario 12: How to display Top N and Bottom N Customers by Sales in single sheet

We can show Top N and Bottom N customers by Sales in single sheet with help of these functions.

Method 1: Using First( ) & Last( )

Method 2: Using Index()

Method 3: Using Rank( )

Note: Here I am going to use Rank( )

Step 1: Drag “Customer Name” to Row and Sales to Column shelf. Sort Sum(Sales) in descending order.

Step 2: Create Calculated field “Rank of Sales”.

Rank(Sum([Sales]))

Step 3: Create a parameter “Top N & Bottom N” with 3 as default value and integer data type.

Step 4: Create calculated field “Top, Middle & Bottom”.

If [Rank of Sales] <= [Top N & Bottom N] Then "Top N"

Elseif (size() - [Rank of Sales]) < [Top N & Bottom N] Then "Bottom N"

Else "Middle"

END

Step 5: Drag Calculated field “Top, Middle & Bottom” to filter shelf, select only Top N and Bottom N.

Step 6: Drag Calculated field “Top, Middle & Bottom” to color shelf.

Note: Let’s assume that we want to see Each Region wise Top N and Bottom N Customers by Sales. Then

Step 7: Drag Region to Row Shelf.

Step 8: Right Click on “Top, Middle & Bottom” column which there in filter shelf, Compute Using and then Select Pane Down.

Step 9: Right Click on “Top, Middle & Bottom” column which there in Color shelf, Compute Using and then Select Pane Down.