2 Replies Latest reply on Jun 5, 2013 12:21 AM by Shalin Bhatt

    Conditional formatting- Color

    Shalin Bhatt

      I am looking for a specific functionality in conditional formatting based on color. I have sales data by several categories, in a bar chart. I wish to color code the chart based on whether a category is performing better than the average across categories. So green if it is better than the category average, and red if it is lower than the category average. How should I be able to do this?

        • 1. Re: Conditional formatting- Color
          Jim Wahl

          Create a calculated field: Category Performance =

          IF SUM([Sales]) > WINDOW_AVG(SUM([Sales])) THEN "Above average"

          ELSEIF SUM([Sales]) < WINDOW_AVG(SUM([Sales])) THEN "Below average"

          ELSE "Average"

          END

           

          Now you can drop this on the color button / shelf.

           

          This formula includes table calc that averages the SUM(Sales) value for all categories---if you have 10 categories, it's averaging 10 values.

           

          That sounds like what you want, but if you want to know if the average sale for a category is above the company average sale, you would use AVG(Sales) > TOTAL(AVG(Sales). AVG(Sales) will be calculated for each cell and TOTAL(AVG(Sales)) will be computed across the entire data set----as if you moved sales to a new view and selected AVG. ...

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Conditional formatting- Color
            Shalin Bhatt

            That worked, thanks a lot Jim