DAX Ratio and Share Measures: Calculating Percent of Total, Percent of Parent, and Contribution Margins with ALLSELECTED and ALLEXCEPT

Introduction

Imagine you're presenting sales performance to your leadership team. You have a matrix visual showing revenue by region and product category. A VP leans over and asks: "What percentage of our total revenue does the Northeast region represent? And within the Northeast, what share does Electronics contribute?" You navigate to the numbers, do some quick mental math, and stumble. Those two questions sound similar, but they require fundamentally different calculations — and building them correctly in DAX is where many analysts get stuck.

Ratio and share measures are the backbone of virtually every business report. Percent of total, percent of parent, and contribution margin calculations appear in sales dashboards, financial statements, marketing attribution reports, and supply chain analyses. The challenge is that these calculations require you to selectively ignore parts of the filter context — keeping some filters intact while removing others. That's exactly what ALL, ALLSELECTED, and ALLEXCEPT are designed to do.

By the end of this lesson, you'll know how to build these measures correctly from scratch, understand when each function is the right tool, and avoid the common traps that cause these calculations to produce wrong results.

What you'll learn:

• How to calculate percent of total using ALL and ALLSELECTED, and the critical difference between them
• How to calculate percent of parent (e.g., category share within a region) using ALLEXCEPT
• How to build a contribution margin percentage measure that respects user-applied filters
• Why filter context manipulation is the core skill behind all ratio measures
• How to handle edge cases like division by zero and blank totals gracefully

Prerequisites

Before diving in, you should be comfortable with the basics of how DAX measures evaluate in a filter context — the idea that every measure calculation happens inside a specific set of active filters imposed by rows, columns, slicers, and visual interactions. If that concept is still fuzzy, work through Master DAX CALCULATE and Filter Context in Power BI and Row Context vs Filter Context: The Mental Model Every DAX User Needs before continuing.

You should also understand what CALCULATE does at a basic level and be familiar with measures versus calculated columns. If you're unsure which to use and when, DAX Fundamentals: When to Use Calculated Columns vs Measures in Power BI will get you oriented.

---

The Core Problem: Why Ratio Measures Are Tricky

Let's ground this in a realistic data model. Suppose you have a retail business with three tables:

• Sales: one row per transaction, with columns OrderDate, ProductKey, CustomerKey, Revenue, COGS (cost of goods sold)
• Products: ProductKey, ProductName, Category, Subcategory
• Regions: RegionKey, RegionName, Country

You've built a base measure:

Total Revenue = SUM(Sales[Revenue])

Now you drop this into a matrix with Category on rows and RegionName on columns. The measure correctly shows revenue for each intersection. But how do you add a column showing each category's share of the grand total?

The naive instinct is to write something like:

-- ❌ This is WRONG - do not use this
Revenue Share % = DIVIDE([Total Revenue], SUM(Sales[Revenue]))

This will always return 100% because the denominator uses the same filter context as the numerator — both are filtered to whatever row and column you're on. The denominator isn't "all revenue," it's "revenue for this cell."

The insight here is that to calculate a ratio, you need the numerator and denominator to live in different filter contexts simultaneously. The numerator follows the current cell's context; the denominator needs some filters removed.

---

Percent of Grand Total with ALL

ALL is the simplest tool for removing filters. When used inside CALCULATE, it wipes out all filters on the specified table or columns, returning the denominator as if no visual filters existed.

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

Let's unpack what's happening here:

1. Numerator: [Total Revenue] evaluates in the current filter context — say, Electronics in the Northeast.
2. Denominator: CALCULATE([Total Revenue], ALL(Sales)) ignores all filters on the Sales table entirely, returning the company-wide total revenue.
3. DIVIDE: Handles division by zero gracefully, returning blank instead of an error.

If you drop this measure into your matrix, every cell will show its share of the absolute grand total revenue — the number you'd see in the matrix's total cell.

This works perfectly for a static grand total. But here's where the trap appears: what happens when a user applies a slicer?

Suppose your report has a slicer for Country, and the user selects "United States." Your Revenue % of Grand Total measure will now show each cell's share of the US-only total — because ALL(Sales) removes filters from the Sales table, but the Country slicer filters through the Regions table. That might be exactly right. Or it might be exactly wrong, depending on what your report is supposed to communicate.

---

Percent of Selected Total with ALLSELECTED

ALLSELECTED is the function that unlocks "percent of what the user chose to see." It removes filters from the current visual context (the row and column position in the matrix) but preserves any external slicer or page-level filters the user has applied.

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

The behavior difference is subtle but critical:

Scenario	ALL result	ALLSELECTED result
No slicer applied	% of all-time total	% of all-time total
Country slicer = "United States"	% of all-time total	% of US-only total
Year slicer = 2024	% of all-time total	% of 2024-only total

In practice, ALLSELECTED is almost always what users actually want in a dashboard. When someone filters a report to North America, they want shares to re-denominate to North America's total — not the global total.

Let's build this fully with proper formatting and variable usage for readability:

Revenue % of Selected Total =
VAR CurrentRevenue = [Total Revenue]
VAR SelectedTotal =
    CALCULATE(
        [Total Revenue],
        ALLSELECTED(Sales)
    )
RETURN
    DIVIDE(CurrentRevenue, SelectedTotal)

Using variables like this makes the logic easier to read and debug. If you're not yet using VAR and RETURN in your measures, DAX Variables in Practice: Using VAR and RETURN to Simplify Complex Measures is worth a read.

---

Percent of Parent with ALLEXCEPT

Now for the more sophisticated scenario: percent of parent. This answers questions like "what share of each region's revenue does Electronics represent?" rather than "what share of the total?"

ALLEXCEPT works by removing all filters from a table except for filters on the columns you specify. It's essentially the inverse of ALL — instead of saying "remove all filters," you say "remove all filters except these specific ones."

Let's build a measure that shows each Category's share within its Region:

Revenue % of Region =
VAR CurrentRevenue = [Total Revenue]
VAR RegionTotal =
    CALCULATE(
        [Total Revenue],
        ALLEXCEPT(Products, Products[Category])
    )
RETURN
    DIVIDE(CurrentRevenue, RegionTotal)

Wait — that's not quite right. Let's think through what we actually want to remove.

When you're looking at Electronics in the Northeast, the active filters are:

• Products[Category] = Electronics (from the matrix row)
• Regions[RegionName] = Northeast (from the matrix column)

To get the Northeast total (denominator), you want to keep the RegionName filter but remove the Category filter. Since the Region filter comes from the Regions table and the Category filter comes from the Products table, you should use:

Revenue % of Region =
VAR CurrentRevenue = [Total Revenue]
VAR RegionTotal =
    CALCULATE(
        [Total Revenue],
        ALL(Products[Category])
    )
RETURN
    DIVIDE(CurrentRevenue, RegionTotal)

This removes only the Category filter, leaving the Region filter intact. So for Electronics in Northeast, the denominator becomes all Northeast revenue. For Electronics in Southeast, the denominator becomes all Southeast revenue. Each cell computes its category's share within its own column's region.

Now let's use ALLEXCEPT for the more complex case where you have a multi-level hierarchy — say, Category at the top level and Subcategory nested beneath it. If your matrix has Category on rows and Subcategory drilled in beneath it, you want each Subcategory's share to be computed within its parent Category:

Revenue % of Category =
VAR CurrentRevenue = [Total Revenue]
VAR CategoryTotal =
    CALCULATE(
        [Total Revenue],
        ALLEXCEPT(Products, Products[Category])
    )
RETURN
    DIVIDE(CurrentRevenue, CategoryTotal)

Here, ALLEXCEPT(Products, Products[Category]) removes all filters from the Products table except the Category filter. So if you're looking at Laptops (a Subcategory) within Electronics (the Category) in the Northeast, the denominator becomes Electronics revenue in the Northeast — the true parent total.

---

Building a Contribution Margin Percentage

Contribution margin is a critical financial metric: it measures how much revenue contributes to covering fixed costs after paying variable costs. The formula is:

Contribution Margin % = (Revenue − COGS) / Revenue

But the interesting version of this measure shows each product's or category's contribution margin relative to the total contribution margin, respecting whatever filters the user has applied. This is where ALLSELECTED shines in financial reporting.

First, build the base measures:

Total Revenue = SUM(Sales[Revenue])

Total COGS = SUM(Sales[COGS])

Contribution Margin =
[Total Revenue] - [Total COGS]

Contribution Margin % =
DIVIDE([Contribution Margin], [Total Revenue])

Now build the share of total contribution margin:

CM Share of Selected Total =
VAR CurrentCM = [Contribution Margin]
VAR SelectedCM =
    CALCULATE(
        [Contribution Margin],
        ALLSELECTED(Sales)
    )
RETURN
    DIVIDE(CurrentCM, SelectedCM)

This tells you: "Of all the contribution margin generated across the products the user is currently looking at, how much does this cell account for?" If a user filters to Q3 and the West region, the denominator automatically becomes total Q3 West contribution margin.

You can layer this further — for instance, showing contribution margin share within category:

CM % Within Category =
VAR CurrentCM = [Contribution Margin]
VAR CategoryCM =
    CALCULATE(
        [Contribution Margin],
        ALL(Products[Subcategory], Products[ProductName])
    )
RETURN
    DIVIDE(CurrentCM, CategoryCM)

This removes subcategory and product-level filters but keeps the category filter, giving you each product's contribution to category-level margin. This kind of measure is invaluable for financial reporting dashboards where management wants to understand which products are driving margin concentration.

---

When Totals Behave Unexpectedly

One gotcha that trips up even experienced DAX developers: ratio measures often show strange values in the subtotal and grand total rows of a matrix.

Consider Revenue % of Selected Total. In a grand total row, the filter context has no row-level Category filter. So ALLSELECTED(Sales) and the unfiltered [Total Revenue] both return the same number — the result is 100%, which is technically correct but visually confusing.

You can handle this with a conditional check:

Revenue % of Selected Total (Clean) =
VAR CurrentRevenue = [Total Revenue]
VAR SelectedTotal =
    CALCULATE(
        [Total Revenue],
        ALLSELECTED(Sales)
    )
VAR IsGrandTotal = CurrentRevenue = SelectedTotal
RETURN
    IF(IsGrandTotal, BLANK(), DIVIDE(CurrentRevenue, SelectedTotal))

This returns blank for the grand total row, which Power BI renders as empty — cleaner than "100%". However, be cautious: if it's genuinely possible that a single product or category represents 100% of revenue, this approach would incorrectly blank that out too.

A more robust approach uses ISINSCOPE, which checks whether a specific column is actively being used as a grouping axis in the visual:

Revenue % of Selected Total (Robust) =
IF(
    ISINSCOPE(Products[Category]),
    DIVIDE(
        [Total Revenue],
        CALCULATE([Total Revenue], ALLSELECTED(Sales))
    ),
    BLANK()
)

ISINSCOPE is part of the DAX information function family — functions that let your measure "look around" at what context it's evaluating inside. You can learn much more about this pattern in Mastering DAX Information Functions: Building Smart Measures with HASONEVALUE, ISFILTERED, and ISCROSSFILTERED.

---

Hands-On Exercise

Let's put it all together. Open Power BI Desktop, load a simple dataset (the Contoso sample data works well, or any dataset with sales transactions, products, and regions), and build the following five measures in order:

Step 1: Create the base measure.

Total Revenue = SUM(Sales[SalesAmount])

Step 2: Build percent of grand total.

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

Step 3: Build percent of selected total.

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

Step 4: Build percent of parent category (for a subcategory-level matrix).

Revenue % of Parent Category =
VAR CurrentRevenue = [Total Revenue]
VAR ParentTotal =
    CALCULATE(
        [Total Revenue],
        ALLEXCEPT(Product, Product[Category])
    )
RETURN
    DIVIDE(CurrentRevenue, ParentTotal)

Step 5: Create a matrix visual with Category on rows and Channel (or RegionName) on columns. Add all three share measures as values.

Then add a slicer for Year or Country and observe how Revenue % of Grand Total stays fixed while Revenue % of Selected Total re-denominates based on your slicer selection. This is the key behavioral difference you need to understand before using these measures in production.

Finally, drill down into subcategories in the matrix and verify that Revenue % of Parent Category always sums to 100% within each category column.

---

Common Mistakes & Troubleshooting

Mistake 1: Using ALL when you should use ALLSELECTED Your percent of total doesn't change when users apply slicers. Check whether you used ALL instead of ALLSELECTED. ALL ignores everything, including slicer choices. Switch to ALLSELECTED unless you genuinely want the absolute grand total as denominator.

Mistake 2: ALLEXCEPT referencing the wrong table Your percent-of-parent returns 100% everywhere. This often means the filter you meant to remove is on a different table than the one you passed to ALLEXCEPT. Trace the filter carefully: if Category is on the Products table and Region is on a separate Regions table, you need ALL(Products[Category]) — not ALLEXCEPT(Regions, Regions[RegionName]) — to remove the category filter while keeping region.

Mistake 3: Denominator in matrix totals causes 100% Your grand total row shows 100% instead of blank. Use ISINSCOPE or HASONEVALUE to detect when you're in a subtotal row and return BLANK() instead.

Mistake 4: Division by zero not handled You see errors in cells where no sales occurred. Always wrap ratio calculations in DIVIDE rather than using the / operator directly.

Mistake 5: Filter context from multiple tables You have a matrix with Category on rows and a slicer on the Regions table, and your ALLSELECTED(Products) denominator isn't behaving as expected. Remember that ALLSELECTED respects external filters on all tables, not just the one specified. If you want to restore specific filters, you may need to combine CALCULATE with explicit KEEPFILTERS calls.

---

Summary & Next Steps

Ratio and share measures are among the most requested calculations in any Power BI project. Here's what you now know how to build:

• ALL for absolute grand total shares: the denominator ignores all filters, including user slicers. Use when you need to show share of a fixed baseline.
• ALLSELECTED for contextual shares: the denominator respects user slicer choices but removes visual-level row and column filters. Use in almost all dashboard scenarios.
• ALLEXCEPT for parent-level shares: removes all filters on a table except the ones you specify, enabling "share within parent" calculations across hierarchies.
• Contribution margin ratios: layer the same denominator logic on any derived measure, not just simple sums.
• ISINSCOPE for clean total rows: conditionally blank out ratio values in grand total rows where the result would be misleading.

The mental model that ties all of this together is simple: every ratio measure needs a numerator in the current context and a denominator in a controlled, broader context. Your entire job as a DAX developer is choosing exactly which filters to relax for the denominator — and ALL, ALLSELECTED, and ALLEXCEPT give you precise control over that choice.

From here, consider exploring DAX Ranking Patterns in Practice: RANKX, TOPN, and Dense vs. Sparse Rankings Across Dynamic Filter Contexts — ranking measures use similar filter context manipulation techniques and pair naturally with share measures in executive dashboards. You might also explore Advanced DAX Patterns: Variables, SWITCH, and Iterator Functions to learn how to make your ratio measures dynamically switch behavior based on user selections, which is the next level of sophistication for self-service analytics.