Excel
All Top 50 Excel & Business Modeling Questions#32
Displaying Hierarchical Contributions with % of Parent Row Total
EasyMcKinseyInterview Question #32
Asked at McKinseyIn a sales Pivot Table with Region ('Americas', 'EMEA') and nested Country ('USA', 'Canada', 'UK', 'Germany'), Revenue is displayed in dollars. Configure the Pivot Table so that each country shows its percentage contribution to its parent Region (e.g. USA + Canada = 100%), rather than percentage of the grand global total.
Input Table: RegionalHierarchy
4 rows preview| Region | Country | Revenue |
|---|---|---|
| Americas | USA | 70000 |
| Americas | Canada | 30000 |
| EMEA | UK | 40000 |
| EMEA | Germany | 60000 |
Expected Output Structure6 rows
| Region / Country | Revenue ($) | Contribution to Region (% of Parent) |
|---|---|---|
| Americas | $100,000 | 100.0% |
| USA | $70,000 | 70.0% |
| Canada | $30,000 | 30.0% |
| EMEA | $100,000 | 100.0% |
| UK | $40,000 | 40.0% |
| Germany | $60,000 | 60.0% |
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 | Region | Country | Revenue | |||
| 2 | Americas | USA | 70000 | Target [Enter Formula] | ||
| 3 | Americas | Canada | 30000 | |||
| 4 | EMEA | UK | 40000 | |||
| 5 | EMEA | Germany | 60000 | |||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect