In the previous chapter, we learnt what Sets are and how to create and use them. We saw an example of Top 5 customers by Sales. When we use this set, we can either use it as IN/OUT, which is a Boolean output, and which when used in our view, will give us two In and Out headers, or we can use it as Show Members in Set, which will only give us the names of our top five customers and filter the rest of the customers.
Now, what if we don't want to filter any customer, but instead group the customers who don't belong in the top five set (that is, the Out customers) under one header called Others? So, what we essentially want to show is the names of the top five customers by sales, that is, all the In customers and the rest of the customers to be grouped as Others.
Since we already have a set that gives us the customers belonging to the top five list and which customers do not belong to that list, we will use this set and achieve our objective. This is one example of how we can use a Set in the calculation.
Let us see how this can be done.
For this recipe, we will use our Set named Top 5 customers by Sales, which we created in the previous chapter. As discussed, we will use this set to show the names of the top five customers and group the rest of the members as Others. Since we already have created the set on the Sample - Superstore.xlsx data, we will continue using the same dataset and create a new sheet in our existing workbook.
Let's get started.
- We will begin by creating a new sheet and rename it Sets in Calculation.
- We will then right click on the Set named Top 5 customers by Sales from the Sets pane (just below the Dimensions and Measures pane) and select the option of Create Calculated Field….
- Let us name this calculation Top 5 + Others and type IF [Top 5 customers by Sales] THEN [Customer Name] ELSE 'Others' END.
- Refer to the following screenshot:
- Let's click on OK and then drag this new calculated field from the Dimensions pane and drop it into the Rows shelf followed by dragging and dropping the Sales field in the Text shelf. Refer to the following screenshot:
- For the final finishing touch, let us sort this view in a way that Others is shown at the very bottom of the list. We can use the ascending sorting option from the toolbar. Refer to the following screenshot:
What we saw was one example of using a Set in a calculation field. The logic that we used was fairly simple. All that we did was use a Logic statement, which basically tested whether the Customer was In the set or Out of the set. If the condition was true, then we fetched the names of the customers, or else we created a label called Others.