Calculated Field in Pivot Table

[chouna]

I have a pivot table with columns represented by Departments (Sales,Finance,Marketing, etc.) I would like to add a calculated field which will be a combination of Sales+Marketing and another calculated field which is Finance+Accounts. How do I do that?

 

[KenWright]

Personally for something like that if it is feasible with your data, I would just add it into the source data by virtue of Extra columns, and then just pull it as necessary via the Field chooser. I prefer real columns of data wherever possible.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[chouna]

Hi Ken,

I could do it in the source data as you suggested but here are the problems:

1. If I use the same field name Depts (meaning Sales+Marketing will be treated as one of the departments, when I do column totals it comes together with the total of other departments

2. If I use another field name (e.g. Selling) and when I drag it into the column area (along with Depts), the result will be Departments by Selling. Visually, it will be something like Sales-Selling, Marketing-Selling, Finance-Selling which is not what I want.

I want to show a report of Depts and tacked-on to it would be the combination of Sales+Marketing and Finance+Accounts columns.

I hope I am clear on my needs. It is hard to explain the requirement in words but I'm sure you would have encountered this type of situation before.

Thanks
Chouna

 

[01Patrik]

Create another column (in my example, col "M", the original department col "O"). Put this in each row:

=if(O2="Sales";"Sales & Marketing";if(O2="marketing";"Sales & Marketing";if(O2="Finance";"Finance & Accounts";if(O2="Accounts";"Finance & Accounts";O2))))

Autofill down... (feel free to nest more if-functions if necessary.) Name the column something like "Alt dep"

In the pivot table, make sure that you use "Alt dep" instead of the original "departments".

// Patrik

 

[chouna]

Thanks Patrik, but the problem is, I would like to have both the original column Dept as well as the AltDep show in the pivot table. The suggestion for an additional column works if I do not need to calculate the column. Example Data source has the following columns: Year, Dept, AltDept, Cost. I need to show the Dept cost by year by department as well as the AltDept cost which is actually the Dept Cost multiplied by some percentage.