Your starting point
Prerequisites: Use the ideas and vocabulary from lesson 11; revisit its worked examples if needed.
Definition: A calculated field derives a new value from existing fields or constants. Its validity depends on the formula, input definitions, units and treatment of missing or invalid inputs. Division by zero does not produce a meaningful finite ratio.
Translate a question into an operation
If the question concerns total revenue, multiplying units by unit price may be appropriate. If it concerns a conversion rate, divide conversions by the relevant opportunities and state the denominator. The same arithmetic operator can answer different questions, so name the resulting field precisely. “Value” is less informative than revenue_before_tax or conversion_percent.
Check units and boundary cases
Multiplying hours by currency per hour yields currency. Dividing currency by hours yields a rate, not a total. Check a normal row, a zero numerator, a zero denominator and a missing input. A zero numerator with a positive denominator gives zero; a zero denominator makes the ratio undefined. The workbench leaves undefined or invalid calculations blank and reports their count.
Keep row-level and aggregate questions separate
An average of row-level percentages gives every row equal weight. A ratio of total numerators to total denominators weights opportunities appropriately for a pooled rate. Neither calculation should be substituted for the other without explaining the intended estimand. Preserve the input columns, document the derived formula and verify the output against a hand calculation before interpreting a chart.
Two worked examples
Revenue and margin
An order has 5 units at 12 each, producing revenue 60. If its cost is 45, the difference is 15. Profit as a percentage of revenue is 15 ÷ 60 × 100 = 25%. Calling the same 15 divided by cost a margin would change the denominator and produce a different measure.
Undefined rates
Campaign rows contain 3 conversions from 30 clicks and 0 conversions from 0 clicks. The first rate is 10%. The second is undefined because there were no click opportunities. Setting it to 0% would imply observed opportunities with no conversions, which is a different situation. Report that row separately.
Where this becomes useful
A support team calculates minutes per resolved ticket. It must define whether the numerator includes unresolved work and whether a zero resolved count means the rate is unavailable. A field name and formula alone are not enough without aligned inputs.
Make an attempt
Use the Formula Builder with Retail sales. Multiply units by unit_price into checked_revenue. Verify a row manually. Then import a tiny ratio table with a zero denominator, calculate a percentage, and inspect the reported blank result instead of treating it as a zero rate.
Misconception check
Claim: Any blank calculation is evidence of zero activity.
Inspect the correction
It may reflect missing inputs, invalid data or an undefined denominator. Inspect the calculation’s policy before summarizing.
Independent application
Design two calculated fields for a fictional service table. State each formula and unit, manually verify normal and boundary cases, and document what happens with missing inputs or zero denominators. Explain how you would aggregate the fields without averaging incompatible rates.
Completion records practice and self-review. It is not certification of statistical competence.
