Scenario 13: Sets for Top N and Others

Step 1 
Open a new workbook and connect to the Superstore sample data source.
Step 2 
Right-click Customer Name, select Create > Set.
Step 3 
In the Create Set dialog box, do the following: 
  1. In the Name text box, type Top N Customers by Sales.
  2. On the Top tab, select By Field, and then select Sales from the field list and Sum from the aggregation list.

  1. When finished, click OK.
To determine the top N value—that is, the number of top customers you want to show—you can specify a static number or a dynamic parameter. This procedure uses the sample data source's Top Customers parameter. The Top Customers parameter is defined as an integer with allowable values of 5-40, in increments of 5.
Note: If you used a parameter to determine the size of the set, be sure to right-click the parameter and select Show Parameter Control.

Step 4 
Complete the following steps to create the initial view:
  1. From the Sets pane, drag Top N Customers by Sales to the Rows shelf.
  2. From the Dimensions pane, drag Customer Name to the Rows shelf, positioning it to the right of the set.
  3. From the Measures pane, drag Sales to Text on the Marks card.
  4. Click the Descending Sort button  
    on the toolbar to see that the set is working.
Step 5 
In the Sets pane, right-click Top N Customers by Sales and then click Create Calculated Field.
Step 6 
In the Calculated Field dialog box, complete the following steps.
  1. In the Name text box, type Subset Labels.
  2. In the Formula text box, type the following formula to create dynamic labels for the customers in the set.
IF [Top N Customers by Sales]
THEN "Top " + str([Top Customers]) + " Customers"
ELSE "Others"

  1. When finished, click OK.
Step 7 
From the Dimensions pane, drag Subset Labels to the Rows shelf, placing it between the Top N set and the Customer Name dimension.
Step 8 
On the Rows shelf, right-click the Top N set and select Show Header.
This hides the In /Out labels while retaining the sort order, so that your top N subset always appears at the top of the view.
Additional tips for improving the view's functionality (optional)
Here are some additional steps you can take to give your viewers more flexibility in displaying the customer subsets.
Step 1 
Right-click the Data pane and select Create Parameter.
Step 2 
In the Create Parameter dialog box, create the following parameter:
  1. In the Name text box, type Expand or Collapse.
  2. For Data type, select String.
  3. For Allowable Values, select List.
  4. In the List of values, add the values Expand and Collapse.

  1. When finished, click OK.
Step 3 
Select Analysis > Create Calculated Field, and then do the following to create a calculation that uses the parameter you created to enable viewers to specify how to view customers in the Others subset.
  1. In the Name text box, type Customer Names.
  2. In the Formula box, type the following formula:
IF [Expand or Collapse]="Collapse" THEN
IF [Top N Customers by Sales]
THEN [Customer Name]
ELSE "Others" END
ELSE [Customer Name] END

  1. When finished, click OK.
Step 4 
In the Parameters pane, right-click Expand or Collapse parameter and then select Show Parameter Control.
Step 5 
From the Dimensions pane, drag Customer Names to the Rows shelf, and place it directly on top of Customer Name, so that it replaces Customer Name.
Now you can use the Expand or Collapse parameter control to see the names the top N customers, where N is determined by the value set in the Top Customers parameter control—in one group, and the remaining customers individually or rolled up into a single Others entry.