Reversing Sign visually Only and only on certain rows.

[#1]

Hi There,

Ok the problem I have.

I'm using a pivot table that contains positive and negative accounting journal entries.

When I add up the amounts I get the correct amount:

INCOME: 100

               (50)

Total Inc:  50

COSTS:  (200)

               45

Total Costs: -165

TOTAL -125

The pivot table totals are correct.

I would however like to reverse the costs so that they display as positive when negative and vice versa but still remain negative in the background.

Can I set the format of the column at a row level?

i.e.

If Income: Don't do anything. or set the format to be #,##0.00;(#,##0.00)

If Row = Costs Then Display (200) as 200 and display 45 as (45)

e.g.

(#,##0.00);#,##0.00

Any one think of a way of doing this so the pivot chart still calculates correctly but displays COSTS as a positive value?

I tried doing it in the textformat fields of the expression but didn't work.

Thanks

[raghavsurya]

Hi,

you can try using "IF" condition in the expression to show the same.  I have tried to put a sample below.

if (Particulars='Income', num(Sum(Income),'#.##0'), num(Sum(Income)*-1,'#.##0')

Regards,

Raghav

[#3]

Thanks Raghav,

That's close but then the values would become positive and therefore the calcuation in the pivot chart would be wrong. (I think)

It's just the number Format I conditionally want to change not its value.

The expression has a text format but I couldn't get it to work.

Any ideas?

[raghavsurya]

Hi,

The calculation in the Pivot will still pick the value which you have loaded.  The example I have given will only diplay the values in the column the way you want and does not change the value when you do Revenue - Cost in another expression.

[#5 · Accepted solution]

Hi,

If you use the above method, then the pivot table will add the rows together incorrectly since you are multiplying the actual result of the expression by -1.

Try using the num() function, and putting the IF check in the format expression.

Example:

num(sum(DollarAmount), IF(Your_Chart_Dimension = 'Income', '#,##0.00;(#,##0.00)','(#,##0.00);#,##0.00'))

This will give you the same answer no matter what, but will just change the formatting when the number is displayed.

[Anonymous]

Hi,

Just put this into effect. Many thanks.

One issue I have is that for those expressions that I am flipping the sign on, zeros show up as (0) or -0, instead of just as 0. I assume this is because the 0 point, which previously was treated as positive for the sign assignment is now treated as negative instead.

Any thoughts on how this might be addressed. I might resolve by changing the text colour to the same as the background colour to hide it, but this won't help when exporting to excel.

Jonathan

[#7]

You could add a "=0" check to the IF statement:

num(sum(DollarAmount), IF(Your_Chart_Dimension = 'Income' OR sum(DollarAmount) = 0,'#,##0.00;(#,##0.00)','(#,##0.00);#,##0.00'))

I haven't tried this, just guessing it might work...

[Anonymous]

Hi,

With some pretty complex set analysis and exception testing in my expressions I think instead that I will try multiplying by an expected sign, which would be 1 or -1. Granted I would then have to take into account the changed sign when working out comparisons against that column.

Jonathan