Hi - I want to highlight max value in the row of a Pivot table. Please suggest how it can be done.
Regards,
Hi!
You are looking to apply conditional formatting on the field to highlight the max value. Out of box, QuickSight has the ability to apply gradient fill based on the scale of values in your pivot table column. This is only a few clicks to set up and may work well in some cases (see documentation here). For example, the following was set up using this method:
However, this isn’t quite what you are asking for, you just want to highlight the max value. In this case, because the max value is disproportionately higher than the other values, the out of box gradient background fill option gives you almost what you are looking for with very little effort, so I wanted to mention it. Where values are more evenly distributed, the colours would be much closer to one another and this approach would produce a very different visual outcome.
To highlight only the max value, there are a few more steps to go through. Basically, you will still use conditional formatting but first you need to create a calculated field that identifies the max value. You can then produce the following:
(Note that I have switched off alternate row colouring in the pivot table and set all rows to white to emphasize that only a single cell is coloured.)
The following screenshot illustrates the intermediate steps needed to produce this visual. I have created the calculated fields separately for each step for ease of understanding but they can be collapsed into a single field.
The definitions for each are as follows:
{Sum by Nation} = sumOver(newPeopleVaccinatedFirstDoseByPublishDate,[areaName],PRE_AGG)
This produces the total number for each category and returns it for every row in the data (i.e. the output is not an aggregate, if you add it to a pivot table use min or max aggregation on top of it).
{Max Value across All Nations} = maxOver(max({Sum by Nation}),[])
Returns the max value across all nations.
{Nation with Highest Value} = ifelse(sum(newPeopleVaccinatedFirstDoseByPublishDate) = {Max Value across All Nations}, 1 ,0)
Produces a 1/0 flag indicating the category with the max value.
Once you have the pre-requisite calculation(s) defined, you can use it in the conditional formatting settings as follows:
You can find more information on level aware calculations I have used in the calculated fields in the official documentation or on the AWS blog site.
The data set used in the example comes from the UK government’s public coronavirus website and contains the newPeopleVaccinatedFirstDoseByPublishDate metric by date and nation.
3 Likes
Hi Prashant, were you able to apply the steps outlined by Istvan to resolve your issue?