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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect