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:
- In the Name text box, type Top N Customers
by Sales.
- On the Top tab, select By Field, and then select Sales from the field list and Sum from the aggregation list.
- 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:
- From the Sets pane, drag Top N Customers
by Sales to the Rows shelf.
- From the Dimensions pane, drag Customer Name to
the Rows shelf, positioning it to the right of the set.
- From the Measures pane, drag Sales to Text on
the Marks card.
- 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.
- In the Name text box, type Subset Labels.
- 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"
END
THEN "Top " + str([Top Customers]) + " Customers"
ELSE "Others"
END
- 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:
- In the Name text box, type Expand
or Collapse.
- For Data type, select String.
- For Allowable Values, select List.
- In the List of values, add the
values Expand and Collapse.
- 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.
- In the Name text box, type Customer
Names.
- 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
IF [Top N Customers by Sales]
THEN [Customer Name]
ELSE "Others" END
ELSE [Customer Name] END
- 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.