Power BI Percent of Parent and Row Percentages in a Matrix (3 Ways)

My sales manager wanted one matrix for the 2026 review: each region’s share of company sales, and inside every region, each product category’s share of that region. A plain percent of total can’t do both, because the second number needs a different denominator on every level.

In this tutorial, we’ll build that Power BI percent of parent matrix from a 60-order sales file. Then we’ll get row percentages with Show value as (no DAX at all) and rebuild the same numbers with a COLLAPSE visual calculation.

Here’s what we’re building:

East reads 49.30% because it’s East’s share of all $165,701. Laptops under East reads 75.48% because it’s Laptops’ share of East alone. The finished report has three parts:

• A % of Parent measure that switches its denominator with ISINSCOPE.
• A Percent of row total matrix built with Show value as.
• A % of Parent VC visual calculation that uses COLLAPSE.

Before You Start

You only need Power BI Desktop, which is free. Nothing here needs a Pro license until you publish. If you’re new to the tool, our Power BI tutorials start with the basics.

My demo file is SPGuides_Sales_2026.csv: 60 orders from January to September 2026 with OrderID, OrderDate, Region, City, State, ZIP, Category, Product, SalesRep, Quantity and Sales. Only three columns matter for this post: Region, Category and Sales.

Here’s how the sales split by region and category, so you can recreate the file or check your own numbers:

Region	Accessories	Laptops	Monitors	Software	Total
Central	$1,979	–	–	$1,440	$3,419
East	$5,257	$61,656	$9,013	$5,760	$81,686
South	$784	$3,897	$9,432	$2,700	$16,813
West	$1,603	$46,917	$3,203	$12,060	$63,783
Total	$9,623	$112,470	$21,648	$21,960	$165,701

Central sold no Laptops or Monitors. Keep that in mind, because it shows up as blank cells later.

Step 1: Load the Sales File

First we get the CSV into the model as a table called Sales.

1. Open Power BI Desktop and select Home > Get data.
2. Pick Text/CSV in the Common data sources list.
3. Browse to SPGuides_Sales_2026.csv and select Open.
4. Check the preview and select Load.

You’ll see the Get data button on the left of the Home ribbon. Text/CSV sits in the middle of the Common data sources list.

Checkpoint: The Data pane lists a Sales table, and its Sales column has a sigma icon (it’s numeric).

If OrderDate comes in as text, check the Locale for import under File > Options and settings > Options. It doesn’t affect this matrix, though.

Step 2: Create the Total Sales Measure

Every percentage in this post divides one Total Sales value by another, so we create that base measure once. It’s the same pattern I use in the percentage of total by category guide.

1. Select the Sales table in the Data pane.
2. On the Home ribbon, select New measure.
3. Type the formula below and press Enter.
4. On the Measure tools ribbon, set the format to Currency.

Total Sales = SUM(Sales[Sales])

Checkpoint: A Total Sales measure with a calculator icon appears under Sales. In a card it shows $165,701.

Why a measure and not a calculated column? Percentages have to be recalculated for every cell of the matrix, which only measures do. Our post on the difference between a calculated column and a measure explains it in more depth.

Step 3: Write the % of Parent Measure

This is the measure that does the real work. It asks the matrix which level the current cell is on, and picks the parent total for that level.

1. Select New measure in the Calculations group on the Home ribbon.
2. Paste the formula below and press Enter.
3. On Measure tools, click the % button and set decimal places to 2.

Both buttons we need today sit next to each other: New measure for the DAX measure, and New visual calculation for the version at the end of this post.

% of Parent =
SWITCH(TRUE(),
    ISINSCOPE(Sales[Category]), DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Sales[Category]))),
    ISINSCOPE(Sales[Region]), DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Sales[Region]))),
    1)

Here’s what each line does:

• ISINSCOPE(Sales[Category]) is TRUE on a category row (Laptops under East). The denominator removes the Category grouping with ALLSELECTED but keeps Region, so it’s the region’s total.
• ISINSCOPE(Sales[Region]) is TRUE on a region row (East). The denominator removes the Region grouping, so it’s the total of every visible region.
• 1 is the grand total row, which is always 100%.

The order matters. On a Laptops row, Region is grouped too, so ISINSCOPE(Sales[Region]) is also TRUE. SWITCH(TRUE(), …) returns the first match, so the lowest level has to come first. If SWITCH is new to you, see our guide to SWITCH in Power BI DAX.

Microsoft’s ISINSCOPE reference uses this exact pattern for its own % of Parent example. DIVIDE returns BLANK instead of an error if a denominator is 0.

Checkpoint: The Data pane shows a % of Parent measure with the % format. A brand-new measure shows decimals like 0.49 until you click %.

Step 4: Build the Matrix With Region Above Category

The matrix needs a hierarchy on rows: Region first, Category second. That’s what gives each category a parent.

1. Click an empty spot on the canvas and select the Matrix visual.
2. Drag Region to Rows, then drag Category under it.
3. Add Total Sales and % of Parent to Values.
4. Select the + icons next to each region to expand it (or turn on Auto expand under Row headers > Options).

Notice the order in the Rows well. If Category sits above Region, the parent changes and every number flips: you’d see each region’s share of a category instead.

One gotcha from my build: dragging fields between wells didn’t move them in this Power BI Desktop build. Right-click the field and use Move to > Rows instead.

Checkpoint: Four region rows with +/- icons, each expanding into its categories, and a Total row of $165,701 and 100.00%.

For other layouts, such as several value columns side by side, our post on Power BI matrix multiple columns has more examples.

Test the Matrix

Now compare the matrix (the first screenshot in this post) with the source data. Each percentage should match a simple division you can do with a calculator:

Row	Total Sales	% of Parent	Check
Central	$3,419	2.06%	3,419 / 165,701
Central > Accessories	$1,979	57.88%	1,979 / 3,419
East	$81,686	49.30%	81,686 / 165,701
East > Laptops	$61,656	75.48%	61,656 / 81,686
South	$16,813	10.15%	16,813 / 165,701
South > Monitors	$9,432	56.10%	9,432 / 16,813
West	$63,783	38.49%	63,783 / 165,701
Total	$165,701	100.00%	always 100%

The four region rows add up to 100% (2.06 + 49.30 + 10.15 + 38.49), and so do the categories inside each region. East’s four categories are 6.44%, 75.48%, 11.03% and 7.05%.

That’s the difference from a percent of grand total. Laptops under East is 37.21% of the whole company but 75.48% of East. If the company-wide share is what you need, see our list of Power BI DAX functions with examples for CALCULATE and ALL.

Because the denominators use ALLSELECTED, a slicer outside the matrix (say a Region slicer) stays respected, and the visible regions still add up to 100%.

Row Percentages Without DAX: Show Value As > Percent of Row Total

Sometimes you don’t need a hierarchy. You want a grid with regions down the side, categories across the top, and each region’s row adding up to 100%. That’s a row percentage, and the matrix can do it on its own.

1. Add a new Matrix. Put Region in Rows, Category in Columns and Total Sales in Values.
2. Right-click Total Sales in the Values well.
3. Select Show value as > Percent of row total.

The submenu has four choices: No calculation, Percent of grand total, Percent of column total and Percent of row total. There’s no Percent of parent option, which is why the DAX measure exists.

The field is renamed %RT Total Sales. Central is 57.88% Accessories and 42.12% Software, East is 75.48% Laptops, and every Total cell reads 100.00%.

Look at the bottom row: 5.81%, 67.88%, 13.06% and 13.25%. That’s each category’s share of all sales, the same numbers our percentage of total by category post builds with DAX.

Notice that these row percentages match the category rows of the % of Parent measure. With Region on rows and Category on columns, a row’s total is the parent. Use Percent of column total for the opposite direction.

Important: Show value as went gray for me after I moved Category from Columns into Rows. Removing Total Sales from Values and adding it back brought the menu back.

The setting only lives on this one visual. A card or a chart can’t reuse it, so build the measure when you need the number in more than one place.

Same Thing With a Visual Calculation (COLLAPSE)

Visual calculations are DAX formulas that run on the visual instead of the model. Microsoft lists them for Power BI Desktop and the Power BI service, and one of the templates is literally called Percent of parent.

1. Select the Region > Category matrix from Step 4.
2. On the Home ribbon, select New visual calculation.
3. Type the formula below in the formula bar and press Enter (or pick the Percent of parent template from the lower half of the button).
4. Set the Data format to Percentage in the Format pane, then select Back to report.

% of Parent VC = DIVIDE([Total Sales], COLLAPSE([Total Sales], ROWS))

COLLAPSE([Total Sales], ROWS) returns Total Sales one level up the rows axis. On a Laptops row, that’s East. On East, it’s the grand total. So there’s no ISINSCOPE and no SWITCH to maintain.

The numbers match the measure: 0.58 and 0.42 for Central’s categories, 0.49 for East, 1.00 for the total. They show as decimals until you change the format.

COLLAPSE works only in visual calculations, not in measures. To change it later, select the pencil icon next to % of Parent VC in the Values well.

Pro Tip: Need two levels up (a grandparent)? COLLAPSE takes an optional third argument for the number of levels, for example COLLAPSE([Total Sales], ROWS, 2).

Microsoft’s docs list limits worth knowing: you can’t copy or reuse a visual calculation across visuals, can’t pin it to a dashboard, and exports exclude its results. For a visual calculation that compares two fields, see our guide to calculating the percentage of two columns.

Measure vs Show Value As vs Visual Calculation

All three gave the same numbers on my file. The right one depends on where else you need the result:

Approach	Best for	Watch out for
% of Parent measure (ISINSCOPE)	Hierarchies, reuse in other visuals, cards, tooltips	Order of the ISINSCOPE checks; update it if you add a level
Show value as > Percent of row total	A quick row percentage in one matrix	No percent of parent option; one visual only
Visual calculation with COLLAPSE	Percent of parent on one visual without model changes	No reuse across visuals, no dashboard pins, exports skip it

I go with the measure for anything that will be shared. The visual calculation is my pick when I’m exploring a report and don’t want to touch the model.

Troubleshooting

• Every row shows 100%: for Show value as, nothing sits in Columns, so each row is its own total. For the measure, ALLSELECTED wraps the wrong column; it must be the column on that level.
• Category rows show the grand total share: the Region check comes before the Category check in SWITCH. Put the lowest level first.
• The measure shows 0.49 instead of 49.30%: select it and click % on the Measure tools ribbon. For a visual calculation, use Data format in the Format pane.
• Show value as is grayed out: remove the value field from Values and add it again after rearranging Rows and Columns.
• Numbers flip after a layout change: check the order in the Rows well. The field on top is always the parent.

Note: ISINSCOPE isn’t supported in DirectQuery calculated columns or in row-level security rules. In a measure, as here, it’s fine. To color the percentages, see conditional formatting based on a measure.

Where to Take It Next

• Add a third level, such as Product under Category, with one more ISINSCOPE line at the top of the SWITCH. A date hierarchy in Power BI works the same way for Year > Month.
• Keep the percentages honest when filters are on the page: our post on removing filters in Power BI DAX compares ALL, ALLEXCEPT and ALLSELECTED.
• Restrict who sees which regions with row-level security in Power BI; the percentages then follow each viewer’s data.

These guides build on the same measures and matrix:

• Power BI Measure Subtract Two Columns | Add Two Measures in Power BI
• Power BI Group by Measure
• How to Create a Measure Table in Power BI?
• Power BI Measure If Multiple Conditions

Frequently Asked Questions