Calculated Field in Pivot Table

[reberryjr]

I'm trying to create a calculated field in a pivot table. The table's structure is below. The Sum of Sum of Clean Loans (I'll change the title once the data works) should come out to 82. I've also pasted the formula in the calculated field below.

I've never worked with these before, and have been googling/searching for about 75 minutes.

[TABLE="width: 462"]
<colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="163" style="width: 122pt; mso-width-source: userset; mso-width-alt: 5961;"> <col width="188" style="width: 141pt; mso-width-source: userset; mso-width-alt: 6875;"> <tbody>[TR]
[TD="width: 138"]Row Labels[/TD]
[TD="width: 127"]Count of LoanNum[/TD]
[TD="width: 163"]Sum of Exception Count[/TD]
[TD="width: 188"]Sum of Sum of Clean Loans
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tommy Chong
[/TD]
[TD="bgcolor: transparent, align: right"]83[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[cyrilbrd]

Hi, just curious, wouldn't it be possible to just add a column in your table instead of creating a calculated field? Based on the values you shown, 83 - 1 = 82 but this is a count - a sum...

 

[reberryjr]

Ultimately, I'm going to have to have a calculated field at some point, as I also have to provide a calculation like =(83-1)/83. Or, I could be misunderstanding what you're suggesting.

 

[cyrilbrd]

It might be easier to perform the calculation within the excel table prior to the Pivot Table...
In other words add a column to your data.
The Pivot table might not be able to calculate a field that is the subtraction in between a Count and a SUM

 

[reberryjr]

I'll have to find a different way to go about this then. Given the way the data is structured, I don't see a way to perform the calculation in the data set, as there are multiple records per User, and the items being calculated are across several rows.

 

[RoryA]

Calculated fields always use the sum of the fields involved. If you need a record count, you'll have to add a column to the source data that just returns 1 for each row, so that you can sum it in the pivot table. Or, if you have Power Pivot, you can do pretty much any calculation you like.

 

[reberryjr]

I do have PowerPivot, but haven't quite figured out how to make it work yet, as it seemed like a completely different application. I'll try to play with it some more.

 

[RoryA]

With PP you can create measures (calc fields) to count values, then other measures can subtract those measures.

 

[John_w]

Calculated fields always use the sum of the fields involved. If you need a record count, you'll have to add a column to the source data that just returns 1 for each row, so that you can sum it in the pivot table. Or, if you have Power Pivot, you can do pretty much any calculation you like.

In Excel 2016, at least, a calculated field defaults to Sum. However in the Values section you can easily change the field to Count by changing its Values Field Settings, so no need for an extra column.

 

[RoryA]

I think we are talking about different things, John. If you put a numeric field into the Values area it will default to Sum, but that's not a calculated field.