9 Replies Latest reply: Dec 17, 2012 8:52 AM by pcarvalho

# Pivot table calculations

Hello,

I want to change the color of cells in the pivot table below (example) if the value exceeds the value of the previous year.

I tried the use of aggr, above, column etc, but I can not find the right formula.

Can anybody help with this?

 Year 2009 2009 2010 2010 Category Color Label excl incl excl incl bike blue 12 15 13 16 bike orange 14 17 14 17 bike red 13 16 13 16 car blue 45 50 50 55 car orange 30 35 32 35 car red 50 55 50 55

regards, Meike

• ###### Re: Pivot table calculations

Hi,

In expression, Click '+' sign , in background color use like this

=if(Column(1) - Column(3) > 0, blue(), Green())

or

=if(Column(1) - Column(2) > 0, blue(), Green())

i'm not sure of that column number, how many expression you're using. and what are the dimension?

Can you say in detail, or sample post?

Hope it helps

• ###### Re: Pivot table calculations

I dont think formula will work.

• ###### Re: Pivot table calculations

Try with this expression(as Background color expression)

=If(Before(TOTAL Sum(Costs), 2) < Sum(Costs),RGB(255,0,0))

Hope it helps

• ###### Re: Pivot table calculations

Thanks it work!

• ###### Re: Pivot table calculations

Hi

If the Year is a pivoted dimension, then

=If(Before(Column(1)) > Column(1), red(), green())

Its possible that Column(1) wont work, then use

=If(Before(expr) > expr, red(), green())

Where expr is the chart expression

Hope that helps

Jonathan

• ###### Re: Pivot table calculations

Hello,

can someone tell me if a PivotTable is possible make calculations per row?

eg:

row 1: sum(F1)

row 2: max(F1)

row 3: avg(F1)

row 4: Sum(row 1 + row 2)

• ###### Re: Pivot table calculations

Ya you can do it

with RowNo() function

Pick(RowNo(), Sum(F1),Max(F1),Avg(F1),RangeSum(Sum(F1),Max(F1)))

Hope it helps

Celambarasan

• ###### Re: Pivot table calculations

But if I select a row in the pivot table it shows me always the first row value.

• ###### Re: Pivot table calculations

Thanks for the help.

Have you found a way to do this.

example: Pick (RowNumber, Sum (Sales), Avg (Sales), Max (Sales), Min (Sales), sum (Sales)-Min (Sales))