Power BI Percentage of Total by Category (With Filters, Matrix and Per Month)

Say your 2026 sales come to $165,701 and Laptops brought in $112,470 of it, so Laptops is 67.88% of the total.

Keeping that Power BI percentage of total by category right once a filter or slicer is on the page comes down to one choice: divide by CALCULATE with ALL(Sales[Category]) or with ALLSELECTED(Sales[Category]).

In this tutorial, we’ll build the power bi percentage of total by category with filter measures and test them with Region and Category slicers. Then we cover percent of grand total, the no-DAX matrix option, a per-month matrix and a COLLAPSEALL visual calculation.

Every number below comes from that report, so you can check your own results against mine.

The Sample Sales Data We’ll Use

I loaded a CSV with Home > Get data > Text/CSV. The table is called Sales: 60 orders from January to September 2026, with OrderID, OrderDate, Region, City, State, ZIP, Category, Product, SalesRep, Quantity and Sales.

Here’s how the four categories add up:

Category	Sales	Share of total
Laptops	$112,470	67.88%
Software	$21,960	13.25%
Monitors	$21,648	13.06%
Accessories	$9,623	5.81%
Total	$165,701	100.00%

Regions matter for the filter tests. East has $81,686, West $63,783, South $16,813 and Central $3,419.

Every percentage measure starts from one base measure. Select New measure, enter this, and set its format to Currency. If measures are new to you, our guide to adding and subtracting measures in Power BI covers the basics.

Total Sales = SUM(Sales[Sales])

The report page has a Table visual with Category, Total Sales and the three percentage measures we’ll build, plus a Region slicer and a Category slicer. With no slicer selection, all three measures agree:

Pro Tip: Build Total Sales once and point every percentage at [Total Sales]. Change the base logic later and every percentage follows.

Method 1: Percentage of Total by Category With a Filter (Recommended)

Use this one when people filter the page by something other than Category, such as Region, Sales Rep or a date. You want each category’s share of whatever is left after that filter.

Here’s the measure:

% of Category Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Category])))

To add it, select New measure, paste the formula and press Enter. Then, on the Measure tools ribbon, set Format to Percentage (or click the % button) and set the decimal places to 2.

With no filter, Laptops shows 67.88%. Now pick East in the Region slicer:

How does this work?

DIVIDE takes the category’s sales and divides them by a second number. That second number comes from CALCULATE, which evaluates [Total Sales] again after ALL(Sales[Category]) removes the filter on Category.

The table row for Laptops filters Category to Laptops. ALL(Sales[Category]) drops that, so the bottom of the fraction is all categories. The Region filter is on a different column, so it stays.

So in East, Laptops is 75.48% of East sales. Monitors is 11.03%, Software 7.05% and Accessories 6.44%, and the total row reads 100.00%.

The ALL(Sales) measure in the same shot shows Laptops at 37.21% and a total of 49.30%. That’s Laptops in East divided by all $165,701, because ALL(Sales) also drops the Region filter. East is 49.30% of company sales, which matches $81,686.

I usually give people a dropdown instead of a list here. Our walkthrough on adding a dropdown slicer in Power BI shows how to switch the slicer style.

Method 2: Percentage of Selected Categories With ALLSELECTED

Method 1 has one catch. If the slicer filters Category itself, ALL(Sales[Category]) throws that selection away too. For a slicer on Category, use ALLSELECTED instead:

% of Selected Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Sales[Category])))

To test it, I Ctrl+clicked Accessories, Monitors and Software in the Category slicer, so Laptops is out. If multi-select doesn’t work for you, see how to select multiple values in a Power BI slicer.

How does this work?

ALLSELECTED drops the filter that the table row puts on Category, but keeps the filter coming from outside the visual. Here that’s the slicer, so the denominator is the three selected categories: $53,231.

So Software is 41.25%, Monitors 40.67% and Accessories 18.08%. Together they make 100.00%.

The two ALL measures still divide by $165,701. They show Software 13.25%, Monitors 13.06% and Accessories 5.81%, and the total row reads 32.12%. That’s right if you want share of the whole business, and wrong if you want share of what’s selected.

ALL(Sales) vs ALL(Sales[Category]) vs ALLSELECTED: Test Results

Here’s the whole slicer test in one place. The search query “percentage of total by category with filter” is really asking which row of this table you need. For more ways to clear filters, see our post on removing filters in Power BI DAX.

Denominator	No filter (Laptops / total)	Region = East (Laptops / total)	Category slicer without Laptops (Software / total)
CALCULATE(…, ALL(Sales))	67.88% / 100.00%	37.21% / 49.30%	13.25% / 32.12%
CALCULATE(…, ALL(Sales[Category]))	67.88% / 100.00%	75.48% / 100.00%	13.25% / 32.12%
CALCULATE(…, ALLSELECTED(Sales[Category]))	67.88% / 100.00%	75.48% / 100.00%	41.25% / 100.00%

Put simply: ALL(Sales) ignores every filter on the table. ALL(Sales[Category]) ignores only Category filters, slicer included. ALLSELECTED(Sales[Category]) ignores the visual’s own grouping and respects the slicer.

For a page with both a Region slicer and a Category slicer, ALLSELECTED(Sales[Category]) handles both. It gave 75.48% for East and 41.25% for the category test.

Method 3: Percent of Grand Total Measure With ALL(Sales)

Sometimes you do want the share of the whole company, no matter what’s filtered. A card that says “East is 49.30% of sales” is a good example. That’s the percent of grand total measure:

% of Grand Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales)))

It’s the same DIVIDE pattern, but ALL(Sales) clears every filter on the Sales table, so the denominator is always $165,701. If your percentage compares two columns instead, such as profit over sales, our guide to calculating the percentage of two columns is the better fit.

How does this work?

With no filter, it returns the same numbers as the other two: Laptops 67.88%, Software 13.25%, Monitors 13.06%, Accessories 5.81%. The difference only shows up once a slicer is used, as the test table above shows.

DIVIDE also protects you from errors. If the denominator is 0, it returns BLANK instead of an error, which matters for a category that has no sales in a filtered month.

About formatting: a new measure shows decimals like 0.68 until you format it. Select the measure in the Data pane and use the Measure tools ribbon. Format = Percentage turns 0.68 into 67.88%, and the decimal places box controls how many digits you see.

Want to learn the functions behind this? Our list of Power BI DAX functions with examples covers CALCULATE, ALL and DIVIDE in more depth.

Method 4: Percent of Grand Total in a Matrix Without DAX

If you only need percentages in one table or matrix, you don’t have to write a measure at all. Power BI can show any numeric field as a percentage.

Here are the steps:

1. Add a Matrix visual. Put Category in Rows.
2. Add the Sales column to Values twice. You can drag it or use +Add data in the well.
3. Right-click the second Sum of Sales in the Values well.
4. Select Show value as > Percent of grand total.

The column is renamed %GT Sum of Sales and shows Accessories 5.81%, Laptops 67.88%, Monitors 13.06%, Software 13.25% and a 100.00% total. Those match the DAX measures exactly.

How does this work?

Power BI does the math inside that visual: each row’s Sum of Sales over the visual’s total. You get the same answer without adding a measure to the model.

The same menu also offers Percent of column total and Percent of row total, which are handy when the matrix has fields on both rows and columns. For row-wise percentages built with DAX, see calculating the percentage of rows in Power BI.

So here’s the catch: the setting lives on that one visual, so a card or another chart can’t reuse it. Need the number in more places? Write a measure. More matrix layouts are in our post on Power BI matrix multiple columns.

Method 5: Percentage of Total by Category per Month

A common follow-up: “what share of each month’s sales came from each category?” This one doesn’t need a new measure. Put the Method 1 measure in a matrix with months on the rows.

1. Add a Matrix on a new page.
2. Rows: Year Month, a text column with values 2026-01 to 2026-09.
3. Columns: Category.
4. Values: % of Category Total.

Text months sort correctly only with the leading zero (2026-01, not 2026-1). Our post on adding a leading zero to the month in Power BI shows how to build that column.

How does this work?

Each cell is filtered by one month and one category. ALL(Sales[Category]) removes only the category filter, so the denominator is that month’s total. That’s why every month row reads 100.00%.

In January 2026, Laptops is 87.00%, Software 9.95% and Accessories 3.05%. In April it flips: Monitors leads at 48.30%, Software 25.82%, Laptops 17.32% and Accessories 8.56%.

Blank cells mean that category had no sales that month. Monitors, for example, sold nothing in January or July. The Total row shows the full-period shares again: 5.81%, 67.88%, 13.06% and 13.25%.

Need the other direction, each month’s share of a category’s total? Swap the column inside ALL. I added this measure to the same matrix in place of % of Category Total:

% of Category Across Months = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Year Month])))

Now every category column adds up to 100% down the months. Accessories sold 38.24% of its year in July, and the Total column shows each month’s share of all sales, from 3.52% in July to 23.05% in August. To compare one month with the one before it instead, see month-over-month change in Power BI.

So pick the column inside ALL by the question: ALL(Sales[Category]) for a month’s mix, ALL(Sales[Year Month]) for a category’s spread over time. If you use a date column instead of a text month, a date hierarchy in Power BI lets you drill from year to month.

Method 6: Visual Calculation With COLLAPSEALL

Visual calculations are DAX formulas that run on the visual itself, not in the model. The current Microsoft Learn docs list them for Power BI Desktop and the Power BI service with no preview label.

Here’s how I added one:

1. Select the matrix that has Category and Sum of Sales.
2. On the Home ribbon, select New visual calculation. Edit mode opens with a formula bar.
3. Enter the formula below and press Enter.
4. In the Format pane, go to Properties > Data format, apply the settings to Percent of grand total, turn on Percentage format and set Decimal places to 2.
5. Select Back to report at the top left.

Percent of grand total = DIVIDE([Sum of Sales], COLLAPSEALL([Sum of Sales], ROWS))

How does this work?

COLLAPSEALL([Sum of Sales], ROWS) returns Sum of Sales at the top level of the rows axis, which is the visual’s total. DIVIDE puts each row over it.

The first result showed 0.06, 0.68, 0.13, 0.13 and 1.00. After the format change it read 5.81%, 67.88%, 13.06%, 13.25% and 100.00%, the same as every other method.

COLLAPSEALL works only in visual calculations, not in measures. To change the formula later, use the pencil icon on the field in the Values well, or right-click it and pick Edit calculation.

Pro Tip: You don’t have to type the formula. Select the lower half of the New visual calculation button and pick the Percent of grand total template, which uses COLLAPSEALL.

Like Show value as, a visual calculation belongs to one visual. Microsoft’s docs list limits: no copy/paste or reuse across visuals, no pinning to a dashboard, and data exports that exclude visual calculation results.

My Power BI Desktop test didn’t match two of those. Export data on this matrix wrote a CSV that included the Percent of grand total column (5.81%, 67.88% and so on), and copying the whole visual to another page kept the calculation.

Pinning is a Power BI service feature, so I didn’t test it. If an export matters to you, run Export data once and check the file before you rely on it.

Which Method Should You Use?

You need	Use
Category share that follows Region, date or other slicers	Method 1: ALL(Sales[Category])
Selected categories that add up to 100%	Method 2: ALLSELECTED(Sales[Category])
Share of the whole company, ignoring filters	Method 3: ALL(Sales)
A quick percentage column in one table or matrix	Method 4: Show value as > Percent of grand total
Each category’s share of each month	Method 5: Method 1 measure in a month x category matrix
A percent of total that lives on one visual, written in DAX	Method 6: visual calculation with COLLAPSEALL

If you’re not sure, start with ALLSELECTED(Sales[Category]). On my report it gave the expected answer for both slicers, and it’s the one I reach for on pages people filter heavily.

Things to Keep in Mind

• Every row shows 100%: the denominator didn’t remove the category filter. Check that ALL or ALLSELECTED wraps the same column you put in the visual’s rows.
• Percentages don’t add to 100% after a slicer click: you’re using ALL(Sales) or ALL(Sales[Category]) with a Category slicer. Switch to ALLSELECTED(Sales[Category]).
• The measure shows 0.68 instead of 67.88%: set Format to Percentage on the Measure tools ribbon. For a visual calculation, use Data format in the Format pane.
• Slicers on two columns of the same table: Microsoft notes that auto-exist can make an ALL() measure use only existing combinations. If results look off, check this first.
• Category in another table: point ALL or ALLSELECTED at the column you actually slice, for example a Products table, not the Sales table.

These guides use the same 2026 sales file, so they make a good next step:

• How to Convert Month Number to Month Name in Power BI?
• Power BI Zip Code Starting With 0
• How to Remove Leading Zeros in Power BI
• How to add a column with a dropdown list in Power Query

Frequently Asked Questions