How to Create Percentage Measure in Power BI

Calculating percentages in Power BI seems like it should be simple, but it can quickly become frustrating if you're new to DAX measures. Unlike a spreadsheet where you can point to a cell for your "grand total," Power BI requires a more dynamic approach. This article will walk you through creating percentage measures in Power BI for the three most common scenarios you'll encounter.

First, Why Use a Measure Instead of a Calculated Column?

Before writing any formulas, it's important to understand the difference between a Calculated Column and a Measure in Power BI, as this is a common stumbling block. While they might seem similar, they serve very different purposes.

• A Calculated Column creates a new column in your data table. The calculation is performed once for each row during data refresh, and the results are stored in your model. It's static.
• A Measure is a calculation that is performed on the fly, based on the context of your report. It doesn’t create a new column, it's a dynamic calculation that responds to filters, slicers, and whatever you click on in your visuals.

For percentages - like "percent of total sales" or "percent of category traffic" - you almost always want to use a measure. Why? Because you need the calculation to adapt. When a user filters your report for a specific year or region, a measure will recalculate to show the percentage of that filtered total. A calculated column, on the other hand, would still be based on the grand total of the entire, unfiltered dataset.

The DAX Functions You Need for Percentages

DAX (Data Analysis Expressions) is the formula language used in Power BI. While it can seem intimidating, you only need to get comfortable with a few key functions to handle most percentage calculations.

DIVIDE() - Your Safeguard for Division

You can use the slash symbol (/) for division in DAX, but the DIVIDE() function is much safer. It has a built-in ability to handle division by zero errors. If the denominator is zero, you can specify an alternate result (or it'll default to BLANK), which prevents your visuals from breaking.

The syntax looks like this:

DIVIDE(numerator, denominator, [alternate_result])

CALCULATE() - The Most Important Function in DAX

Think of CALCULATE() as your tool for changing the rules. Normally, a measure calculates based on the "filter context" of a visual. If you're looking at a row for the "Electronics" category in a table, an AOV measure only calculates AOV for that category.

CALCULATE() lets you modify that filter context. It takes two main arguments: the expression you want to calculate (like SUM(Sales[Revenue])) and one or more filters you want to apply. It’s what lets you say, "...but for this calculation, ignore the filter on product category."

ALL(), ALLEXCEPT(), and ALLSELECTED() - Your Filter Removers

These functions are used inside CALCULATE() to remove filters from your data. This is the magic behind getting a "total" to divide by.

• ALL(): Removes all filters from a table or specific columns. This is what you'll use to get the true grand total of your entire dataset, regardless of any slicers or filters applied.
• ALLEXCEPT(): Removes all filters from a table except for filters on the columns you specify. This is useful for calculating percentages of a parent category total.
• ALLSELECTED(): Removes filters from the context of the visual, but - and this is a big but - it respects any filters applied to the report from slicers or other visuals. This is for calculating a percentage of the visible total.

Now, let's put these into practice with some real-world examples.

Scenario 1: Calculating Percentage of a Grand Total

This is the most common use case. You have a table showing sales by product category, and you want to add a column that shows what percentage each category contributes to your overall company sales.

Let's assume you have a 'Sales' table with a 'Revenue' column and a 'Products' table with a 'Category' column, and they are related in your data model.

Step 1: Create your base measure

First, we need a simple measure to sum up our revenue. This ensures we have a base calculation to work with.

1. In the Power BI Report view, right-click on your 'Sales' table and select "New measure."
2. Enter the following DAX formula:

Total Revenue = SUM(Sales[Revenue])

Step 2: Create a measure for the grand total revenue

Next, we need a measure that calculates the total revenue, but ignores any filters applied by the visual (like which specific product category is being shown on that row).

1. Right-click your 'Sales' table again and select "New measure."
2. Use CALCULATE() and ALL() to create the measure for the grand total:

Grand Total Revenue = CALCULATE( [Total Revenue], ALL('Sales') )

This formula tells Power BI: "Calculate my [Total Revenue] measure, but do it after removing all filters from the 'Sales' table."

Step 3: Create the final percentage measure

Now we just need to divide the first measure by the second one.

1. Create a new measure.
2. Use the DIVIDE() function to finish it up:

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

Step 4: Format and use your measure

Your measure will show up as a decimal by default (e.g., 0.25). To change this, select your new measure in the Fields pane. The "Measure tools" tab will appear on the ribbon. Find the formatting section and either choose "Percentage" from the dropdown or click the % icon.

Now you can drag this measure into a table, matrix, or chart along with your product categories to see a perfect percentage breakdown of the grand total.

Scenario 2: Calculating Percentage of a Category Total

Sometimes you need to get more granular. Imagine you have product categories and sub-categories. You don't want to see what percent of all company sales "Running Shoes" represents. You want to see what percent of "Footwear" sales "Running Shoes" represents.

This requires calculating a percentage of a sub-total, which calls for the ALLEXCEPT() function.

Step 1: Use the same base measure

We can still use our Total Revenue measure from before. No need to recreate it.

Step 2: Create a measure for the parent category total

Here's where it gets different. We want to remove the filter on sub-category but keep the filter on the parent category.

1. Create a new measure.
2. Use ALLEXCEPT() to keep the parent category filter active:

Parent Category Total Revenue = CALCULATE( [Total Revenue], ALLEXCEPT(Products, Products[Category]) )

This formula tells Power BI: "Calculate the total revenue, but remove all filters from the 'Products' table except for the filter that is currently on the 'Products[Category]' column."

Step 3: Create the percentage measure

Just like before, we divide the part by the whole.

% of Parent Category Revenue = DIVIDE( [Total Revenue], [Parent Category Total Revenue] )

When you put 'Category', 'SubCategory', and this new measure in a matrix, you’ll see how each sub-category contributes to its parent's total sales, which is a much more useful level of insight for category managers.

Scenario 3: Calculating Percentage of a Visible (Filtered) Total

This scenario solves a common complaint. When you use ALL() to get a grand total, it ignores slicers. If you filter your report for the year 2023, you expect the percentages to add up to 100% for that year. But because ALL() grabs the denominator from all time, the percentages will be much smaller and won't add up to 100%.

The fix is to use ALLSELECTED().

Step 1: Your base measure is still the same

Our trusty Total Revenue is good to go.

Step 2: Create a measure for the visible total

Instead of ALL(), let's use ALLSELECTED().

1. Create a new measure.
2. The formula is nearly identical to the grand total measure, but with one key change:

Visible Total Revenue = CALCULATE( [Total Revenue], ALLSELECTED('Sales') )

This measure now calculates a total that respects user-driven filters from slicers or other charts on the report page.

Step 3: Create the final percentage measure

You guessed it, we use DIVIDE() one last time.

% of Visible Total Revenue = DIVIDE( [Total Revenue], [Visible Total Revenue] )

When you add a "Year" slicer to your report and choose a year, you will now see all your percent-of-total calculations reflect only the data from that year, adding up to 100% just as you'd expect.

Final Thoughts

Creating percentage measures in Power BI takes a bit more thought than in a simple spreadsheet, but it's incredibly powerful once you get the hang of it. By leveraging functions like CALCULATE along with ALL, ALLEXCEPT, or ALLSELECTED, you can create dynamic views that precisely calculate percentages against a grand total, a category sub-total, or a user-filtered total.

Mastering these DAX formulas is a huge leap forward in Power BI, but it also highlights how much setup can be involved in getting a seemingly simple answer. We created Graphed because we wanted to eliminate this manual work and bypass the steep learning curve. Instead of writing and debugging DAX, you can simply ask, "show me revenue by category as a percentage of the total for the last 90 days," and our AI data analyst builds the chart for you instantly using real-time data from your connected sources.