When designing our dashboards and widgets, we should always consider the visuals and UI to convey the message of our widgets and help our users to consume the data easily, and take actionable business decisions based on it.
Many times, we wish to present our data in a more granular form, such as a pivot table, which will help the user to perform diagnostics over the raw data.
However, we still need to consider the user interaction with the pivot, to make sure he is not lost in the translation.
In our use case, the sales manager needs the following view to determine business actions: Present a list of customers and purchase data, while highlighting the top customer by total sales, per country.
To answer this need we will need to start off with a pivot table and present the relevant fields and values we wish to include:
Next, we need to consider the highlighting:
1) First, we will create a formula using RANK() function which returns the rank of a value in a list of values. This will indicate who is our top customer.
The value we are ranking is the Order ID in a descending fashion and by Country.
Here is the result of the ranking:
2) Next, we will create another formula that returns the total sales if the rank is 1:
Here's our result:
The reason we perform this calculation is due to the fact we cannot apply conditional coloring when the '# Sales' equals to 1 (it is 14).
3) Now, the top Sales is equal to the formula. Let's set up the conditional filtering:
In the formula section, enter the 'Case When' formula we have previously used:
And set the conditional statement to be equal to the formula, on our case, 14 = 14.