Excel
All Top 50 Excel & Business Modeling Questions#31
Creating Dynamic Calculated Fields in Pivot Tables for Profit Margins
MediumAmazonInterview Question #31
Asked at AmazonYou have a sales transaction table with Revenue in Col C and COGS in Col D. In a Pivot Table summarizing categories, you need a 'Gross Margin %' metric. An analyst writes `=(Revenue - COGS)/Revenue` in the source table and averages it in the Pivot Table, producing mathematically erroneous results. Explain how to create a proper Calculated Field and why simple averaging fails.
Input Table: TransactionSource (A1:D3)
2 rows preview| Category | Transaction | Revenue | COGS |
|---|---|---|---|
| Apparel | Tx 1 (Small) | 100 | 20 |
| Apparel | Tx 2 (Wholesale) | 10000 | 8000 |
Expected Output Structure2 rows
| Calculation Method | Resulting Gross Margin % | Mathematical Accuracy |
|---|---|---|
| Average of Source Column (80% & 20%) | 50.0% | INCORRECT (ignores transaction volume weighting) |
| Pivot Calculated Field: =('Revenue'-'COGS')/'Revenue' | 20.8% | CORRECT: (10100 - 8020) / 10100 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Category | Transaction | Revenue | COGS | ||||||
| 2 | Apparel | Tx 1 (Small) | 100 | 20 | Target [Enter Formula] | |||||
| 3 | Apparel | Tx 2 (Wholesale) | 10000 | 8000 | ||||||
| 4 | ||||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.