Announcements
cancel
Showing results for
Did you mean:
Contributor III

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

Hello,

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

AccountDate 1Date 2

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

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!

1 Solution

Accepted Solutions
MVP

May be this

If((Sum([Revenue (USD)])/Before(Sum([Revenue (USD)])) - 1) > 0.2, 'Green',

If((Sum([Revenue (USD)])/Before(Sum([Revenue (USD)])) - 1) < -0.2, 'Red'))

6 Replies
Champion

Hi,

try something like this:

=If( (Sum([Revenue (USD)]) / before(Sum([Revenue (USD)])))> 0.2 , 'green', ETC

MVP

May be this

If((Sum([Revenue (USD)])/Before(Sum([Revenue (USD)])) - 1) > 0.2, 'Green',

If((Sum([Revenue (USD)])/Before(Sum([Revenue (USD)])) - 1) < -0.2, 'Red'))

Contributor III
Author

Huzzah!  It worked.  This is perfect, and really helps me out.  Thank you for the quick response as well!

Contributor III
Author

Thank you!  This works, and I'm able to tweak that 20% to be whatever I need.  Very helpful

MVP

Just out of curiosity, the expression worked without subtracting 1? I would have thought that to get 0.20, you would need to subtract -1... but I might have missed something.

Contributor III
Author

I'm sorry - you were right, I did end up using yours (with the -1); and therefore updated the "Correct Answer".