# Pivot Table Calculated Fields and Items

How to use Pivot Table Calculated Fields and Items.

## Pivot Table Calculated Fields and Items

Just to explain the difference between Pivot Table Calculated Fields and Items, because it is very important, you need to look at the underlying data. In the underlying data a field is defined as a column in this case, so for example the bill is a field, internet cost is a field, charged to MTN is a field. So if we are going to do a calculated field – what we are asking is to do some sort of calculation in two fields – for example  we are going to work out what the net profit is  and all we are going to do is take the bill, subtract the charge, subtract other costs to get an answer.

This compared to calculated items – here let’s say we have got days of the week, Wednesday, Thursday, Friday etc. We may want to actually add up items within a field – so for example  I want all the Saturdays and  the Sundays to be added up to create a weekend. So the difference between the two is that in calculated fields there are distinct columns that we are doing something to. In calculated items we are actually working within the field and adding, subtracting or some sort of calculation to the items in the field.

Focusing on  the  Calculated Field first.  Here we have a Pivot Table – there are dates, we have got the total bill, the costs for the charge and some other costs. We would like to know what the net profit is. In order to do a calculated field, click anywhere within the Pivot Table. You have the Pivot Table ribbon,  you’ll see there is something called Formulas- when I click on it you’ll see there is something called Calculated Fields. And there is the Calculated Items. At the moment it is greyed out but again we are focusing on Calculated Fields. When I click Calculated Fields the following appears and it asks – give this field a name – I am going to call it Net Profit for example and then you just have to create the formula and the Row Labels or the fields are shown here so we know that the formula is going to be – you want it to take the bill and either click insert field or double click and when I say, in this case,  add  the charge (because it is a negative number) add the other costs. I am happy with that. I could say Add and do another one. But for now I’m going to say OK. You’ll see what it has done,  it has created a new column and if we look at our fields you’ll see there is another item called Net Profit. Now in the underlying data base we already had a Net Profit calculation. I have just shown you how to do it in a calculated field.  If you want you can do some quick checks – you’ll see those add up to .09, .09 and if we actually pull through the proper net profit field, we format  it, you’ll see it gives you the same number.  So that is the way to create a calculated field. The benefit of a calculated field is if I now go and I am going to do some sort of a grouping here – the months is OK, the calculated field will continue to do the calculation based on the information it has. I am just going to ungroup this.