Forum Discussion

harish's avatar
harish
Cloud Apps
12-14-2023

conditional formatting in Pivot Table.

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

 

Dashboards & Reporting

5 Replies

Replies have been turned off for this discussion
  • Benji_PaldiTeam's avatar
    Benji_PaldiTeam
    Data Pipeline
    12-14-2023

    Hi harish ,

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

    •  

       

    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. 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. 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 🙂
    [email protected] 

    Paldi Solutions, Number #1 Sisense Plugins Developer

    • harish's avatar
      harish
      Cloud Apps
      12-14-2023

      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  and  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 bottom3
      which is wrong.

      How do i solve this .