Say we want to show the the highest-selling states (by number of orders) per region in our Sample Superstore dataset as pictured below. How do we do it?
#1. Create the map
- Double-click on the 'State/Province' field to generate the map
- Change the Marks type to 'Map'
- Right-click on the 'Order ID' field, and drag it to the Detail card. This will bring up a small dialog box. Select the Count Distinct option:
CNTD(Order ID) - Drag the 'Region' field to the Colour card
At this point, your view will look something like this:
#2. Use a Table Calculation to create Rank
- Right-click on the 'Order ID' field, and drag it to the Detail card. This will bring up a small dialog box. Select the Count Distinct option:
CNTD(Order ID)(We create this again so we can use it to compute the rank) - On this second 'CNTD (Order ID)` field, click on the drop down arrow (or right-click on the field) and select: Add Table Calculation
- The dialog box that pops up allows us to define our table calculation. Under Calculation Type, select Rank
- Under Compute Using, select Specific Dimensions if not already selected
- 'State/Province' is already checked, which is what we want
Here's Andy Kriebel's trick is to understanding a table calculation: read from the bottom up.
- Start with the unchecked boxes and add the words "For each" before those.
- Move to the Calculation type and add the words "compute the" before the item in the Calculation Type box.
- Add the word "by" before each box that is checked at the bottom, again reading it from bottom to top.
In our example, we would read the above as:
- For each Region (blue)
- Compute Rank (orange)
- By State/Province (green)
#3. Edit the labels
- Change each of the 'CNTD (Order ID)` fields to Label by selecting the icon to their left as shown below, and edit the labels as desired.
#4. Filter to top 3
- To filter to the top 3 states, we need to first change our rank ( the 'CNTD (Order ID)` field with a delta symbol on it) to a discrete field.
- Then, we can right-click on it and select Filter as shown below:
- Check only 1-3 in the dialog box that pops up. And we're done!