why is this simple calculated field not working

[Siyanna]

Hi All,

I have created a pivot table that looks like this

Count of Passed/Failed	Passed/Failed
Start Date	Failed	Passed	(blank)	Grand Total
Jan	37	190		227
Feb	23	102		125
Oct	52	190		242
Nov	36	143		179
Grand Total	148	625		773

<colgroup><col><col span="3"><col></colgroup><tbody>
</tbody>

the Failed and passed are items of the passed/Failed field
All i want to do is add a calculated field that just takes the difference from passed and diff and then add this to the pivot chart but it does not work saying i cant add formula

this is the steps im following on excel 2013

highlight pivot table
Analyze
fields, item, sets
Calculated field
name = diff
formula =Passed-Failed (i have even tried ='Passed'-'Failed' with no luck

Hopefully some can explain how to get around this

Thank YOu

 

[RoryA]

Calculated fields only use SUM for their field aggregations. If you don't have Power Pivot, you can add new counter columns to the data source that return either 1 or 0 as applicable and then sum those as a count.

 

[Siyanna]

thank you

this is what ive done - added 2 columns that returns does this

DiffPassed =IF([@[Passed/Failed]]="Passed",1,0)
DiffFailed = =IF([@[Passed/Failed]]="Failed",1,0)

still dont get the difference - all im trying to do is add the difference - thank you

 

[RoryA]

You want a calculated item, not a calculated field, if Passed and Failed are in the same column.

 

[Siyanna]

i tried adding a calculated item also but getting the message cant add it to a grouped field - what steps do i need to follow

Really appreciate it

 

[RoryA]

You'll have to ungroup the field, or use your two new columns to create a calculated field. What problem did you have with the latter?

 

[Siyanna]

Hi - i managed to get around it by doing the following

Added 1 column that gave 1 if it was passed and the same for another column if it failed

for the pivot table - i added the sum of these 2 columns in the values field and the count was then used adding a calculated field ising these 2 new columns

thanks for your help

 

[jcr0ss0v3r]

I’m having the same problem, formula I’m using is =if(amount > 100000, 3%* amount, 0) but result is zero