Calculating Percentages of Totals with CALCULATE and ALL

Introduction

In our last lesson, you learned how to use CALCULATE with the ALL function. This powerful combination allows you to create measures that calculate a total value while ignoring filters from your visuals. Specifically, you created a [Grand Total Revenue] measure that always shows the total sales, regardless of the product category on a given row. You successfully built the "Whole" part of a percentage calculation.

Today, we will complete the formula. This lesson focuses on combining the "Part" (the individual row's value) with the "Whole" (the grand total) to calculate one of the most fundamental metrics in business intelligence: the percentage of a grand total. We'll also extend this concept to calculate percentages of subtotals. By the end of this 60-minute lesson, you'll be able to confidently answer questions like, "What percentage of our total revenue does each product category represent?"

The Anatomy of a Percentage Calculation

At its core, a percentage is a simple ratio:
$\text{Percentage}=\frac{\text{Part}}{\text{Whole}}$

In our DAX context:

• The Part (Numerator): This is the value in the current filter context. Our existing [Total Revenue] measure already gives us this. When placed in a table row for "Bikes," it calculates the revenue just for bikes.
• The Whole (Denominator): This is the total we want to compare against. As you learned previously, we create this by using CALCULATE to remove the relevant filters.

To perform this division safely in DAX, we'll use a specific function.

The DIVIDE Function: Your Shield Against Errors

While you could use the forward slash (/) for division, it's a best practice in DAX to use the DIVIDE function. Why? Because it automatically handles division-by-zero errors. If you have a category with no sales, its total revenue (and thus the denominator in some calculations) could be zero or BLANK. Using / would break your visual with an error, whereas DIVIDE will gracefully return BLANK or a specified alternative result.

The syntax is: DIVIDE(numerator, denominator, [alternate_result])

To understand the core functions for today's lesson, let's start with a quick read.

Step-by-Step: Calculating Percentage of Grand Total

Let's apply this theory to answer our business question: "What percentage of total revenue does each product category contribute?"

Step 1: The Formula

We will combine our [Total Revenue] measure (the numerator) with the pattern you learned last lesson for the denominator.

1. In Power BI, create a New measure in your Sales table.

2. Enter the following DAX formula:

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

How It Works:

• [Total Revenue] is our numerator, evaluated in the current context of the visual (e.g., for the "Bikes" row).
• CALCULATE([Total Revenue], ALL(Sales)) is our denominator. The ALL(Sales) part removes all filters from the Sales table, so it always calculates the grand total revenue, regardless of the row.
• DIVIDE safely performs the division.

Step 2: Formatting

A raw decimal like 0.31 is hard to read. Let's format it as a percentage.

1. Select your new [% of Grand Total Revenue] measure in the Fields pane.
2. In the Measure tools tab that appears in the ribbon, find the Formatting section.
3. Click the '%' symbol or select "Percentage" from the dropdown menu. You can also set the number of decimal places.

Step 3: Verification

Create a Table visual and add the following fields:

1. Product[Category]
2. [Total Revenue]
3. [% of Grand Total Revenue]

The table will clearly show each category's contribution, and the percentages in the new column should sum to 100%.

For a guided walkthrough of this entire process, the following video is excellent.

Improving Readability with Variables (VAR)

As your DAX formulas become more complex, using variables makes them much easier to read, debug, and maintain. A variable stores the result of an expression, which you can then reference multiple times. You define them with VAR and state the final calculation with RETURN.

Let's rewrite our percentage measure using variables. The result is identical, but the logic is far clearer.

% of Grand Total Revenue (VAR) = 
VAR NumeratorValue = [Total Revenue]
VAR DenominatorValue = 
    CALCULATE(
        [Total Revenue],
        ALL(Sales)
    )
RETURN
    DIVIDE( NumeratorValue, DenominatorValue )

This structure neatly separates the "Part" from the "Whole" before combining them, which is a highly recommended professional practice.

Calculating Percentage of a Subtotal

What if the business question is different? "For products sold in the Clothing category, what percentage of clothing sales does each color represent?"

This requires a percentage of a subtotal, not the grand total. The logic is similar, but we need to be more precise with our ALL function. Instead of removing all filters with ALL(Sales), we only want to remove the filter on the product color to get the category total.

The Formula:

% of Category Revenue = 
DIVIDE(
    [Total Revenue],
    CALCULATE(
        [Total Revenue],
        ALL('Product'[Color])
    )
)

How It Works:
The denominator CALCULATE([Total Revenue], ALL('Product'[Color])) is the key. In a table containing Product[Category] and Product[Color], this expression calculates the revenue by removing the filter on Color but respecting the filter on Category.

To see a detailed example of this, let's watch another video.

A Deeper Dive: The True Role of ALL

You have a preference for understanding the "why," which is critical for mastering DAX. There's a subtle but crucial detail about how ALL works inside CALCULATE. It's often described as "returning a table of all values," but its true role is simpler and more powerful: it acts as a filter remover.

Thinking of ALL as a filter remover, rather than a function that returns a table, will prevent confusion in more complex scenarios later on. For a precise explanation from the experts, please review the following article.

Conclusion

In this lesson, you have put all the pieces together to perform one of the most common and essential analytical tasks. By combining your base measure with a modified denominator, you can provide powerful context to your data.

Key Takeaways:

• Percentage calculations follow a DIVIDE(Part, Whole) structure.
• The DIVIDE function is the safest way to perform division in DAX.
• To calculate a percentage of a grand total, the denominator uses CALCULATE with ALL(FactTable) to remove all filters.
• To calculate a percentage of a subtotal, the denominator uses CALCULATE with ALL(DimensionColumn) to remove a specific filter while keeping others.
• Using variables (VAR/RETURN) significantly improves the readability and maintenance of your DAX code.

Preview of the Next Lesson:
We've now seen how to calculate a subtotal percentage by telling DAX which filter to remove (e.g., ALL('Product'[Color])). But what if a table has 10 columns and you want to remove 9 of them? The formula would get very long. In our next lesson, we will learn about the ALLEXCEPT function, which allows you to do the opposite: specify which filters you want to keep, making subtotal calculations even more elegant and efficient.