conditional formatting in Pivot Table.

Question

Hii, I have pivot table merchant names and sales , now i want to color the&nbsp; top 3 and bottom 3 of sales column , top 3 should be green and bottom 3 should be red . How can i achieve that&nbsp;

benji_palditeam · Answer

Hi&nbsp;harish&nbsp;,
Option 1: This solution can be applied if the pivot is sorted by the measure.

Add the dimension (e.g., 'Country') and the measure (e.g., 'Total Cost') to Pivot
&nbsp;Select 'Conditional' in the color picker to specify conditions for coloring. Here, we need apply green to the top 3 rows and red to the bottom 3 rows.
Click the formula button for the first condition and insert the provided formula, ensuring to apply filter on the Country dimension in the formula. This will apply color to top 3 rows of the pivot. Here what we are doing is - taking the 3rd largest 'Total Cost' and apply color to all values in the pivot that are greater than this calculated value. Note that, 'Greater that or equal to' symbol is selected in the condition as highlighted in blue rectangle below
Similarly, configure the second condition by clicking its formula button and applying the provided formula, with the filter on the Country dimension.&nbsp;This will apply color to bottom 3 rows of the pivot. Here what we are doing is - taking the 3rd lowest 'Total Cost' and apply color to all values in the pivot that are lower than this calculated value.&nbsp;Note that, 'Less that or equal to' symbol is selected in the condition as highlighted in blue rectangle below

Click OK to apply the coloring.

&nbsp;

Option 2:
Use the Expandable Pivot plugin, which features an excellent tool known as Custom Data Format. This robust tool allows the application of custom rules to value cells, enabling the presentation of values in specific formats, merging two cell values into one, and more.&nbsp;A custom Paldi API is exposed for JavaScript developers, offering the capability to customize values. Each developer-added customization appears in the widget. Dashboard designers can effortlessly implement these customizations by selecting the desired options.&nbsp;Customizations added at the global level can be reused across any pivot, while those added at the widget level are specific to that widget.
Few example are:

Feel free to reach out if you have further questions, we're always happy to help 🙂inquiries@paldi.solutions&nbsp;
Paldi Solutions, Number #1 Sisense Plugins Developer

harish · Answer

.&nbsp;&nbsp;

harish · Answer

But i have dynamic filters , like country , bank and month ,i have kept the country = Thailand and bank as Aeon and month as 2023 October.the pivot table displays the merchants and sales . then i am following you method&nbsp; and&nbsp; its not working , i am applying filter on merchants to get top 3 and bottom 3 it is not taking the merchants which are displayed after applying the dynamic filters instead of that it is taking among all merchants which are top3 and bottom3which is wrong.How do i solve this .&nbsp;

benji_palditeam · Answer

Hi&nbsp;harish&nbsp;,
Is it working if you disable all the filters applied on filter panel (on right side)?
How did you applied filter to get top 3 and bottom 3? Is it using Ranking filter?

&nbsp;
Feel free to reach out if you have further questions, we're always happy to help&nbsp;
inquiries@paldi.solutions&nbsp;
Paldi Solutions, Number #1 Sisense Plugins Developer
&nbsp;

dray · Answer

Hello&nbsp;harish.
Were you able to get this resolved?

Forum Discussion

conditional formatting in Pivot Table.

Recent Discussions

Pivot Table widget - column headers and column data out of alignment

Blox conditional formatting up and down indicators

Remove Widget Title Box

Change Legend Category Names

Rename the Pivot Grand Total

Sisense

Support

Resources