Help with calculated fields

[DrBacon]

Hello all,

I have a question when using calculated fields in pivot tables. I have a set of surveys, where each number means either negative, null, or positive. What I want the calculated field to do is the following formula:

(count of all positives - count of all negatives)/sum(positives+negatives+nulls)

what I tried to do is the following (the field being calculated is called OS or "overall satisfaction")

=((OS="Positive")-(OS="Negative"))/((OS="Positive")+(OS="Null")+(OS="Negative")

But the result just returns a 1

What could I do?

Cheers
Bacon

 

[Misca]

Is this the NPS score you're calculating? You're on the right track: Just multiply the percentage you're getting with 100 and round to 0 decimals.

The whole formula should look something like:

=ROUND((COUNTIFS(B2:B51;"Positive")-COUNTIFS(B2:B51;"Negative"))/ROWS(B2:B51)*100;0)

(Or if you're not trying to calculate the NPS just format the number from the original formula as percentage.)

 

[DrBacon]

I thought you could not use ranges in Calculated fields in pivot tables?

But yes, this is an NPS

 

[Misca]

Since you're already using Pivot Tables you might want to do the whole thing in Power Pivot.

To do this you're basically going to need two types of formulas:

Answers:=COUNTROWS(YourTableName)
Positives:=CALCULATE([Answers],YourTableName[YourColumnName]>8)
Negatives:=CALCULATE([Answers],YourTableName[YourColumnName]<7)

NPS:=ROUND(DIVIDE([Positives]-[Negatives],[Answers])*100,0)

 

[DrBacon]

I have never used Power Pivot, I have it enabled, but not sure how to use it?

 

[Misca]

There are tons of Power Pivot Tutorials to be found in YouTube. Here's one that might get you started: https://youtu.be/yjASjwJCIxA