Calculating Sum of Two Fields in Pivot Table using Calculated Field

[seaottr]

Hi there,

I'm having trouble calculating the values of two fields in my pivot table using Calculated Field. The value field settings is being displayed as % of row total. The Column labels (Detractor, Passive and Promoter) are taken from the field called "NPS2". I'm trying to get the pivot to display the following results (Promoter - Detractor). I've tried a whole bunch of things after looking through countless forums. I'm new to pivot tables and the Calculated Field dialogue. According to other forums, I should just be able to enter "= ('Promoter'-'Detractor')" or "= (Promoter - Detractor)" in the Calculate Field formula, but that doesn't work. Here's the data in the pivot.

Row Labels	Detractor	Passive	Promoter	Grand Total
7/4/2016	0.00%	50.00%	50.00%	100.00%
7/5/2016	14.29%	28.57%	57.14%	100.00%
7/6/2016	50.00%	50.00%	0.00%	100.00%

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Can anybody help please?

 

[khabi21]

Did you try using the "Difference From" function in the "Show Values As" calculation?

Otherwise, I don't believe PivotTables are your solution. The PivotTable does a good job of representing data that you have. If you then want to do more manipulation, you have to do it manually.

 

[seaottr]

Unfortunately that doesn't fix it.

The problem with doing it manually won't allow it to be dynamic based on the user's selections. The calculation/formula output is based on the values for a specific date range selected.

Did you try using the "Difference From" function in the "Show Values As" calculation?

Otherwise, I don't believe PivotTables are your solution. The PivotTable does a good job of representing data that you have. If you then want to do more manipulation, you have to do it manually.

 

[liveinhope]

I'm a bit confused about what you want to do here. Your post title is Calculating Sum of Two Fields in Pivot Table using Calculated Field but then your calculated field examples is "(Promoter - Detractor)" (IE subtracting one from the other)

do you looking for the total of "promoter" and "detractor" or the difference

 

[seaottr]

I know I'm having difficulty explaining what I need to do exactly...The below might help:

The file in the link below includes the sample information and an example of what I want my pivot table to display. I ultimately want to create a calculated field (I think) that displays ((% of Promoter) - (% of Detractor)) as a two decimal number.

Link to file: https://drive.google.com/file/d/0B9O...ew?usp=sharing

 

[seaottr]

Thanks everyone for your help...I was able to figure it out using a calculated field. I just needed to add a total survey column to my data set since I can't reference row labels in my calculated field formula.