5 Replies Latest reply: May 11, 2018 12:09 PM by Joey Lake RSS

    Use Color Expression to highlight difference greater than 20% of comparison

    Joey Lake

      Hello,

       

      I have a Pivot table that is comparing an accounts revenue between two dates; looks like this:

       

      AccountDate 1Date 2
      Advertiser A205.40199.65
      Advertiser B543.99201.01
      Advertiser C199.20465.77

       

      Currently, I'm using a background color expression to highlight whether the number value in "date 2" is higher or lower than the data in the corresponding "Date 1" column.

       

      This is my expression: If(Sum([Revenue (USD)])>before(Sum([Revenue (USD)])), 'green', If(Sum([Revenue (USD)])<before(Sum([Revenue (USD)])), 'red'))

       

      This is working as expected.  Applied to the example above it yields:

       

      AccountDate 1Date 2
      Advertiser A205.40199.65
      Advertiser B543.99201.01
      Advertiser C199.20465.77

       

      The problem I'm having, is I really only want it to highlight if it's a significant enough difference to warrant it.  Sy 20% more or less.  So in the case of Advertiser A; I'm not really concerned, that it spent $6 less; it's within an acceptable range to not be of concern.  But in both other cases, the differences between Dates 1 and 2 are significant enough to highlight.

       

      My ultimate data set has 75 or so advertisers it's comparing day over day, and it turns into a bit of noise when trying to pick out those that may need the most attention.

       

      Is there a way to modify the expression to not be a little more flexible?

       

      Thanks!