Excel Pivot Table Hack: Display Percentages of Parent Row Totals

Excel Pivot Table Hack: Display Percentages of Parent Row Totals

by | Dec 2, 2025 | Excel

Piv­ot tables offer a pow­er­ful way to ana­lyze data, par­tic­u­lar­ly when you need to under­stand pro­por­tions with­in hier­ar­chi­cal cat­e­gories. In this tuto­r­i­al, I’ll show you how to quick­ly add sums in a piv­ot table and dis­play val­ues as a per­cent­age of their par­ent row, allow­ing for imme­di­ate visu­al­iza­tion of how indi­vid­ual items con­tribute to their cat­e­go­ry totals.

This tech­nique is espe­cial­ly valu­able when ana­lyz­ing sales data across prod­uct cat­e­gories and indi­vid­ual items.

Watch my step-by-step video tuto­r­i­al below to see this process in action:

Setting Up Your Pivot Table

To begin cre­at­ing an infor­ma­tive piv­ot table with per­cent­age cal­cu­la­tions, we need to start with a sim­ple dataset that con­tains hier­ar­chi­cal infor­ma­tion. In my exam­ple, I’m using a dataset that includes cat­e­gories (such as fruits, veg­eta­bles, and sweets) along with the spe­cif­ic prod­ucts with­in each cat­e­go­ry and their cor­re­spond­ing rev­enue figures.

The process of cre­at­ing the piv­ot table is straightforward:

  1. Select your data range
  2. Nav­i­gate to the Insert tab in the Excel ribbon
  3. Click on “Piv­ot Table”
  4. Choose to place the piv­ot table on an exist­ing work­sheet (I select­ed cell F1 in my demonstration)
  5. Click “OK” to cre­ate the basic piv­ot table structure

Once your piv­ot table frame­work is estab­lished, you’ll need to struc­ture it prop­er­ly to show both cat­e­gories and their con­stituent prod­ucts. In the Piv­ot­Table Fields pan­el, drag the appro­pri­ate fields to build your hier­ar­chi­cal view.

Structuring Your Pivot Table

For prop­er hier­ar­chi­cal analy­sis, you’ll want to arrange your fields in a log­i­cal order. In the Rows sec­tion of the Piv­ot­Table Fields pan­el, add your Cat­e­go­ry field first, fol­lowed by the Prod­uct field. This cre­ates a nest­ed struc­ture where prod­ucts appear under their respec­tive categories.

For the val­ues sec­tion, we need to add the Rev­enue field twice — once to show the raw sum and once to show the per­cent­age of par­ent. Sim­ply drag the Rev­enue field to the Val­ues area twice. By default, Excel will sum these val­ues, which is exact­ly what we want for this analysis.

Changing the Layout

By default, Excel dis­plays piv­ot tables in com­pact form, but for bet­ter read­abil­i­ty, I pre­fer the tab­u­lar lay­out. To change this:

  1. Go to the Design tab under Piv­ot­Table Tools
  2. Click on “Report Layout”
  3. Select “Show in Tab­u­lar Form”

This adjust­ment sep­a­rates the Cat­e­go­ry and Prod­uct into dis­tinct columns, mak­ing your data more read­able and eas­i­er to ana­lyze at a glance.

Adding Percentage of Parent Row

Now comes the key part — trans­form­ing one of our rev­enue columns to show per­cent­age of par­ent row. This cal­cu­la­tion will show how each prod­uct con­tributes pro­por­tion­al­ly to its cat­e­go­ry total, and how each cat­e­go­ry con­tributes to the grand total. Fol­low these steps:

  1. Right-click on any cell with­in the sec­ond Sum of Rev­enue column
  2. Select “Show Val­ues As” from the con­text menu
  3. Choose “% of Par­ent Row Total”

This sim­ple change trans­forms the raw num­bers into per­cent­ages, giv­ing you imme­di­ate insight into the pro­por­tion­al con­tri­bu­tion of each item. For instance, in my exam­ple, you can now see that apples rep­re­sent approx­i­mate­ly 35% of all fruit sales, while the fruits cat­e­go­ry as a whole rep­re­sents about 41% of total sales across all categories.

Understanding the Results

After apply­ing the per­cent­age of par­ent row cal­cu­la­tion, your piv­ot table auto­mat­i­cal­ly adjusts to show mean­ing­ful pro­por­tions at every level:

  • Indi­vid­ual prod­ucts show their per­cent­age con­tri­bu­tion to their imme­di­ate category
  • Cat­e­go­ry subto­tals show their per­cent­age con­tri­bu­tion to the grand total
  • The grand total always equals 100%

In my demon­stra­tion, this clear­ly showed that sweets account­ed for approx­i­mate­ly 40% of total sales, veg­eta­bles for about 18%, and fruits for approx­i­mate­ly 41%. With­in each cat­e­go­ry, you can sim­i­lar­ly see the pro­por­tion­al con­tri­bu­tion of each product.

Finalizing Your Pivot Table

To make your piv­ot table more under­stand­able, it’s impor­tant to rename the col­umn head­ers to accu­rate­ly reflect what each col­umn rep­re­sents. In our case:

  1. Change the head­ing of the first sum col­umn to sim­ply read “Sum”
  2. Rename the sec­ond col­umn to “Per­cent­age of Parent”

These descrip­tive head­ers ensure that any­one view­ing your piv­ot table will imme­di­ate­ly under­stand what the num­bers rep­re­sent with­out need­ing addi­tion­al explanation.

With these adjust­ments com­plete, you now have a com­pre­hen­sive piv­ot table that not only shows the raw rev­enue fig­ures but also clear­ly illus­trates the pro­por­tion­al rela­tion­ships between cat­e­gories and their con­stituent prod­ucts. This dual-view approach pro­vides both absolute and rel­a­tive per­spec­tives on your data, enabling more nuanced analy­sis and decision-making.

This tech­nique is par­tic­u­lar­ly valu­able when ana­lyz­ing sales per­for­mance, bud­get allo­ca­tions, or any hier­ar­chi­cal data where under­stand­ing pro­por­tion­al rela­tion­ships is impor­tant. By visu­al­iz­ing both raw num­bers and per­cent­ages simul­ta­ne­ous­ly, you gain deep­er insights into your data struc­ture and can more effec­tive­ly com­mu­ni­cate those insights to others.

How to Calculate Hierarchy Levels in Power Query

How to Calculate Hierarchy Levels in Power Query

by | Nov 22, 2025 | Excel

In this tuto­r­i­al, I demon­strate a prac­ti­cal appli­ca­tion of recur­sive func­tions in Pow­er Query to cal­cu­late hier­ar­chy lev­els in orga­ni­za­tion­al struc­tures or MLM sys­tems. Using a cus­tom func­tion with recur­sion, we can effi­cient­ly deter­mine each per­son­’s posi­tion in a hier­ar­chi­cal struc­ture based on their refer­rer rela­tion­ships, pro­vid­ing valu­able insights for orga­ni­za­tion­al analy­sis and reporting.

Under­stand­ing hier­ar­chy lev­els is essen­tial for visu­al­iz­ing report­ing struc­tures, track­ing MLM down­lines, or map­ping any par­ent-child rela­tion­ships in your data.

Understanding the Problem: Hierarchy Levels in Organizations

In many orga­ni­za­tion­al struc­tures, par­tic­u­lar­ly in mul­ti-lev­el mar­ket­ing (MLM) sys­tems or cor­po­rate hier­ar­chies, under­stand­ing the lev­el depth of each mem­ber is cru­cial. The lev­el rep­re­sents how many steps a per­son is from the top of the orga­ni­za­tion. For instance, in our exam­ple, John is at the top (lev­el 0), N is direct­ly below John (lev­el 1), and Thomas is below N (lev­el 2).

Our sam­ple data con­tains an ID col­umn that unique­ly iden­ti­fies each per­son and a Refer­rer col­umn that indi­cates who brought that per­son into the orga­ni­za­tion. The refer­ral rela­tion­ship estab­lish­es the hier­ar­chi­cal struc­ture we need to ana­lyze. Our goal is to cal­cu­late each per­son­’s hier­ar­chy lev­el auto­mat­i­cal­ly using Pow­er Query’s recur­sive capabilities.

Setting Up Power Query

To begin work­ing with our data, we need to import it into Pow­er Query where we can cre­ate and apply our recur­sive function:

  1. Select any cell in your data table
  2. Go to the Data tab
  3. Click From Table/Range to import your data into Pow­er Query

This imports your data con­tain­ing the ID and Refer­rer columns into the Pow­er Query Edi­tor, where we can start build­ing our solution.

Creating a Recursive Function to Calculate Hierarchy Levels

The core of our solu­tion is a cus­tom func­tion that can call itself (recur­sion) to track up through the hier­ar­chy until it reach­es the top. Here’s how to cre­ate it:

  1. In the Pow­er Query Edi­tor, go to the Home tab
  2. Click New Source > Oth­er Sources > Blank Query

Now we need to define our func­tion. Our func­tion will require two para­me­ters: the per­son­’s ID we want to cal­cu­late the lev­el for and the com­plete table of peo­ple data to ref­er­ence. The func­tion will:

  1. Find the row for the cur­rent person
  2. Get their refer­rer’s ID
  3. Check if they have a referrer
  4. If they don’t (they’re at the top), return 0
  5. If they do have a refer­rer, call the same func­tion for the refer­rer and add 1 to the result

The M code for our func­tion looks like this:

(per­son­ID, personTable) =>
let
per­son­Row = Table.SelectRows(personTable, each [ID] = personID){0},
per­son­AboveID = personRow[Referrer],
result = if per­son­AboveID = null
then 0
else @HierarchyLevel(personAboveID, per­son­Table) + 1
in
result

Make sure to name your func­tion Hier­ar­chyLev­el so that the recur­sive ref­er­ence to itself works prop­er­ly. The @ sym­bol in front of the func­tion name empha­sizes that we’re call­ing the same func­tion again.

Applying the Function to Our Data

After cre­at­ing our func­tion, we need to apply it to every row in our data table:

  1. Go back to your main query with the import­ed data
  2. Click Add Col­umn > Invoke Cus­tom Function
  3. Select your Hier­ar­chyLev­el function
  4. For per­son­ID, select the ID column
  5. For per­son­Table, we need to ref­er­ence the cur­rent table

When set­ting the per­son­Table para­me­ter, we ini­tial­ly might try to ref­er­ence a col­umn name, but this will gen­er­ate errors. Instead, we need to ref­er­ence the entire table from the pre­vi­ous step. In Pow­er Query, we can do this by refer­ring to the pre­vi­ous step name.

Once cor­rect­ly con­fig­ured, the func­tion will cal­cu­late the hier­ar­chy lev­el for each per­son in our table. Thomas, who is referred by N (ID 2) who in turn is referred by John (ID 1), will show as lev­el 2. John, who has no refer­rer, will be at lev­el 0.

Testing and Validating the Recursion

To ver­i­fy our func­tion works cor­rect­ly, we can exam­ine the cal­cu­lat­ed lev­els for each per­son in our organization:

  • John (ID 1): Lev­el 0 (top of hier­ar­chy, no referrer)
  • Anne (ID 2): Lev­el 1 (referred by John)
  • Thomas (ID 5): Lev­el 2 (referred by Anne)
  • Paul: Lev­el 3 (referred by Thomas)

We can fur­ther test by chang­ing refer­rer rela­tion­ships. For exam­ple, if we change Paul’s refer­rer from Thomas (ID 5) to some­one who is already at lev­el 3, Paul would then become lev­el 4. After mak­ing such changes in the source data, we can sim­ply refresh our Pow­er Query to see the updat­ed hier­ar­chy levels.

Handling Edge Cases in Your Hierarchy

Our recur­sive func­tion han­dles sev­er­al impor­tant edge cases:

  1. Top-lev­el mem­bers (those with null refer­rers) are assigned lev­el 0
  2. The func­tion will work for orga­ni­za­tions of any depth, con­tin­u­ing to recurse up the chain until it reach­es the top
  3. If the data con­tains cir­cu­lar ref­er­ences (Per­son A refers to Per­son B who refers back to Per­son A), the recur­sion could cre­ate an infi­nite loop — con­sid­er adding error han­dling for this sce­nario in real applications

In a real-world sce­nario, you might want to enhance this func­tion to han­dle more com­plex require­ments, such as detect­ing cir­cu­lar ref­er­ences or pro­cess­ing mul­ti­ple hier­ar­chies with­in the same dataset.

Loading the Results Back to Excel

Once you’re sat­is­fied with your hier­ar­chy lev­el calculations:

  1. In the Pow­er Query Edi­tor, go to Home > Close & Load
  2. Your data table with the new hier­ar­chy lev­el col­umn will appear in Excel
  3. Any time your hier­ar­chy changes, sim­ply refresh the query to recal­cu­late all levels

This pow­er­ful tech­nique allows you to main­tain an up-to-date view of your orga­ni­za­tion­al struc­ture with min­i­mal effort. The recur­sive approach effi­cient­ly han­dles even large hier­ar­chies with­out requir­ing com­plex for­mu­las or man­u­al tracking.

With this solu­tion in place, you can eas­i­ly build reports and visu­al­iza­tions that lever­age hier­ar­chy lev­el infor­ma­tion, enabling bet­ter insights into your orga­ni­za­tion­al struc­ture, MLM down­lines, or any hier­ar­chi­cal data you’re work­ing with.