why is this simple calculated field not working

[Siyanna]

Hi All,

I have created a pivot table that looks like this

[TABLE="width: 565"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Count of Passed/Failed[/TD]
[TD]Passed/Failed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]Failed[/TD]
[TD]Passed[/TD]
[TD](blank)[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]190[/TD]
[TD][/TD]
[TD="align: right"]227[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]102[/TD]
[TD][/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]190[/TD]
[TD][/TD]
[TD="align: right"]242[/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]143[/TD]
[TD][/TD]
[TD="align: right"]179[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]625[/TD]
[TD][/TD]
[TD="align: right"]773[/TD]
[/TR]
</tbody>[/TABLE]


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