Calculated Field

[Alan_CT06]

I would like to create a calculated field in my pivot table.

My pivot table has three columns: City, Acct# and AcctStatus.

In the AcctStatus column, the entries are either "New" or "Existing".

In my Pivot Table, City is the Row Label and the Values are Count of Acct# and Count of AcctStatus.

I'd like to have a calculated column that gave a count of the rows for which the entry in AcctStatus column = "New", but I'm not making the proper entry in the Insert Calculated Field Formula Box.

What should go there?

Thanks,

 

[Cubist]

Maybe I'm not understanding. If you want to look at the counts for "New" only, why can't you add the AcctStatus to Filter?

 

[Alex Blakenburg]

I'm afraid you can't do that with a normal Pivot Table.
One of the Limitations of pivot table calculated fields is:
"When referring to other pivot fields in a calculated field, the SUM of their values is always used (see example below). Any other functions or operations in the formula will apply to those SUMs"
Since Acct is text and does not have a Sum, it can't be used in the calculated field.
The easiest option is to add a column (field) NewAcct to your initial table with an if statement putting a 1 or 0 on each line and using that as a field in your pivot table.
An alternative would be to use PowerPivot.

Ref for quote: How to Create Excel Pivot Table Calculated Field Examples

 

[Alan_CT06]

Maybe I'm not understanding. If you want to look at the counts for "New" only, why can't you add the AcctStatus to Filter?

Yes the filter would work. Haven't worked much with Pivot Tables.

Thank you.

 

[Alan_CT06]

I'm afraid you can't do that with a normal Pivot Table.
One of the Limitations of pivot table calculated fields is:
"When referring to other pivot fields in a calculated field, the SUM of their values is always used (see example below). Any other functions or operations in the formula will apply to those SUMs"
Since Acct is text and does not have a Sum, it can't be used in the calculated field.
The easiest option is to add a column (field) NewAcct to your initial table with an if statement putting a 1 or 0 on each line and using that as a field in your pivot table.
An alternative would be to use PowerPivot.

Ref for quote: How to Create Excel Pivot Table Calculated Field Examples

Thank you. I'll check out PowerPivot also.

 

[Alex Blakenburg]

PS: I should have said Acct Status instead of Acct but they are both text so the same principle applies.