Microsoft Excel 2013 - Knowledge Requested about Calculated Fields in Pivot Tables

[beadedbytes]

I've recently created some calculated fields inside a Excel 2013 Pivot table. And, there seems to be some 'unexpecteds' when using them.

Let's say I have a field with Source_Name='Rsp_Prcnt'. After creating the calculated field, I've assigned the metric Custom_Name='Resp%' as the Column Label. FYI ... This is just one of 5 calculated fields in the Pivot Table.

When I deselect this field from the pivot view and then reselect it, the field label changes back to Source_Name 'Sum of Rsp_Prcnt'. The Custom_Name needs to re-established and the resulting values need to be formatted again as a percent.

Also, if I want to move just one of the calc fields from column to row, I can only do so by moving ALL the calculated fields to row labels.

First, are there workarounds for these particular issues?

Second, is there a good summary document of the pros/cons of Calculated Fields in Pivot Tables? I've come across a few docs on the internet with cautionary information about how these fields treat Totals in Pivots and am curious about other things that one should be mindful of when using this feature in Excel.

 

[SkipVought]

Hi,

So you have a field In your source data for your Pivot Table that has a column heading in your source data of Rsp_Prcnt, which is a percent value in your source data. Is that correct?

If so, using that percent value in a calculated field would be very VERY suspect IMNSHO. Even just using the percent field simply in a summary field (Sum of Rsp_Prcnt) can be very risky, be pending on how your percent field was originally calculated. Aggregations of aggregations often produce bad results!

 

[beadedbytes]

Skip --

"So you have a field In your source data for your Pivot Table that has a column heading in your source data of Rsp_Prcnt, which is a percent value in your source data. Is that correct?"

The answer to your question is 'No'.

Calculated field is named Rsp_Prcnt (See under 'Field Settings. It would be listed under 'Source_Name'.)

The 'Rsp_Prcnt' calculated field is based on the two fields in the source data table:
'Total # of Responses' divided by 'Total Mailed Qty'

So, if the total mail qty was 1000 and 250 people responded, then the 'Rsp_Prcnt' = .25%. Make sense?

 

[SkipVought]

When I deselect this field from the pivot view and then reselect it, the field label changes back to Source_Name 'Sum of Rsp_Prcnt'. The Custom_Name needs to re-established and the resulting values need to be formatted again as a percent.

That's just the way that Pivot Table Wizard works AFAIK

All you have is a Sum of...

When you remove that field from the Σ Values it looses all the properties you assigned. You just might want to aggregate that values in a different way.

 

[billyjoemc]

This is a very intuitive thread to follow, Ive defintely learnt a lot in excel values and macros.

 

[beadedbytes]

Thanks, Skip. Let me know if there are any good readings on pros/cons of Calculated Fields.

 

[SkipVought]

I prefer adding calculated fields to my Source Data, that the Pivot Table references.