Formula Fields and Calculated Measures
Unlike formula fields, which are created at the dataset level and evaluate results row by row, calculated measures are made at the workbook level using aggregated results. For example, if you create a formula field for sales amounts, each row in the dataset will show one value based on your formula. 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 values. In contrast, calculated measures use the aggregated results of base measures and arithmetic operations to show things like year-over-year variances and profit percentages. You can’t get these kinds of results using formula fields alone.
The following examples show why you get a different result in each case:
Creating a formula field and using it as a measure
In your dataset, you can use this definition to create a formula field that shows profit percentage:
% Profit Formula = {estgrossprofit}/{amount}
The formula field column gives a percentage value for each entry. For example, the % Profit (formula) column shows a value for each of the three entries for 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 this definition to create a calculated measure:
% Profit (calculated measure) = Est. Gross Profit (Sum) / Amount (Sum)
The calculated measure column gives a percentage value based on the aggregated values from 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 pivot table gives 64.82%, while the % Profit (formula) (Avg) column gives 72.67%.
In this example, the formula field measure only shows an average of the percentages from the dataset ((18% + 100% + 100%) / 3 = 72.67%). With the calculated measure, the amount is found by first aggregating 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.