Highlighting Max Value in a Pivot
Analytical Need
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.
However, we still need to consider the user interaction with the pivot, to make sure he is not lost in the translation.
The Challenge
In our use case, the sales manager needs the following view to determining business actions: Present a list of customers and purchase data, while highlighting the top customer by total sales, per country.
Solution
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:
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 1 (it is 14).
3. Now, the top Sales is equal to the formula. Let's set up 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, in our case, 14 = 14.
Final result:
Updated 03-02-2023
