Formula Fields and Calculated Measures
Unlike formula fields which are built at the dataset level to evaluate results on a row by row basis, calculated measures are built at the workbook level using aggregated results. For example, if you create a formula field that calculates sales amounts, each row in the dataset will show a single value based on the formula definition. If you then use that formula field as a measure in a pivot table or chart, the results are simply an aggregate of the formula field results. By contrast, calculated measures use the aggregated results of base measures combined with arithmetic operations to display results such as year over year variances and profit percentages. These types of results are not achievable using formula fields alone.
The following examples describe why you obtain a different result in each case:
Creating a formula field and using it as a measure
In your dataset, you can use the following definition to create a formula field that returns the profit percentage:
% Profit Formula = {estgrossprofit}/{amount}
The formula field column returns a percentage value for each entry. For example, the % Profit (formula) column displays a value for each of the three entries of the Sales Order 100537: 18%, 100%, and 100%.
If you use this formula field as a measure in a pivot table, the returned value is 72.67%, which is not entirely accurate.
(18% + 100% + 100%) / 3 = 72.67%
Creating a calculated measure
In your pivot table, you can use the following definition to create a calculated measure:
% Profit (calculated measure) = Est. Gross Profit (Sum) / Amount (Sum)
The calculated measure column returns a percentage value based on the aggregated values calculated from the Est. Gross Profit (Sum) and the Amount (Sum).
(648.40 + 4,500 + 294) / (3,602 + 4,500 + 294) = 64.82
The % Profit (calculated measure) in the following pivot table returns 64.82% and the %Profit (formula) (Avg) column returns 72.67%.
In this example, the formula field measure is showing only an average of the percentages displayed on the dataset (that is (18% + 100% + 100%) / 3 = 72.67%). With the calculated measure however, the amount is calculated by aggregating the values of the Est. Gross Profit and Amount fields first and then performing the division (that is 648.40 + 4500 + 294 = 5442.4, 3602 + 4500 + 294 = 8396, so 5442.4 / 8396= 64.82%). Therefore, the calculated measure returns the expected value.
For more information about formula fields, see Formula Fields.
For more information about calculated measures, see Calculated Measures.