Pivot Table Help - Calculated Field

[seaottr]

Hi everyone,

I've been wracking my brain for the past two days trying to figure this out. I'm not very proficient with pivot tables, but I'm hoping you gurus can help. I tried explaining myself in a different forum, but I don't think I was making sense...so I've created a worksheet with fake info so you can see what I'm trying to accomplish with my pivot table.

I'm hoping to do this with a pivot table, as the information I need to display (NPS Score) is based on a date range that is shown in the pivot table based on filters selected and therefore the calculations need to be dynamic. If there are easier ways to do this, I'm all open for suggestions, but I need to create a pivot chart out of the information in the pivot table.

The file in the link 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/0B9Oxdy_sFHcZMUt5dlBrNm9KN0k/view?usp=sharing

Your expertise would be GREATLY appreciated!

 

[BITechMacro]

Hello;

You can find your workbook here

for NPS Score, you cannot make a pivot table for one column.

Firstly, Create 3 Calculated fields for Detractor, Passive and Promoter with an IF function.

=IF(NPS<0,-1)
=If(NPS=0;0)
=IF(NPS>0;1)

With Pivot Table, put those fields in columns
Make a right click, Show Values as, % Of Row Total.

Next cell of your pivot table, Create a basic formula, with cell reference. Promoter - Detractor.

You can Show % NPS scrores like this.

 

[seaottr]

Thanks BITechMacro 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.