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.
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.