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
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 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 | ||||||||||
| 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.