
Color background color based on column partial sum
Stefan Wühl Feb 7, 2012 6:46 PM (in response to pbwall2000)if % Sold is maybe calculated like
=sum(Sold) / sum(Available)
then your background color expression may look like
=if( sum(Sold) / sum(Available) >= sum(total Sold) / sum(total Available), red(), blue() )
Hope this helps,
Stefan

Color background color based on column partial sum
pbwall2000 Feb 8, 2012 9:51 AM (in response to Stefan Wühl )Kind of.
Here are the defs:
column(1) = Available = sum(available_count)
column(2) = Sold = sum(sold_count)
column(3) = % Sold = column(2) / column(1)
So, I went into the background color and put this in but it's not working as expected. It's all blue.
=if(column(2)/column(1) >= sum(total column(2)) / sum(total column(1)), red(), blue())

Color background color based on column partial sum
Stefan Wühl Feb 8, 2012 10:04 AM (in response to pbwall2000)I think something like sum(total column(2) ) will not work.
Try
=if( sum(sold_count) / sum(available_count) >= sum(total sold_count)/sum(total available_count), red(), blue())

Color background color based on column partial sum
pbwall2000 Feb 8, 2012 10:22 AM (in response to Stefan Wühl )Thank you. That works!
I can start another post but I just tried making the pivot table a little more multidimensional by adding Sale Year to it, so now it looks like this:
Using the formula above worked when it wasn't by year but now it appear the TOTAL portion of that expression totals all columns up? So, since the overall percentage is 53% (131+61/260+100), Dog collar in 2011 is showing up RED since it's below 53% but really it should be blue because it's above 50% for that specific year. Does that make sense?
Is there a way to get the partial sum for the column instead of the total sum for all the columns?

Color background color based on column partial sum
pbwall2000 Feb 8, 2012 10:25 AM (in response to pbwall2000)I think I figured it out. Need to add the column you want to group by after the total, so this:
=if( sum(sold_count) / sum(available_count) >= sum(total <TheYear> sold_count)/sum(total <TheYear> available_count), red(), blue())
Right?

Color background color based on column partial sum
Stefan Wühl Feb 8, 2012 10:28 AM (in response to pbwall2000)Yes, should be correct. You can list the fields to ignore in the total, so you can still group by, using the <> syntax.




