How to Calculate Percentile in Power BI

Averages can be misleading. Knowing your average order value is helpful, but it doesn't tell you about the high-value customers who skew that average upwards. This is where percentiles come in, giving you a much clearer picture of distribution and performance. This tutorial will walk you through exactly how to calculate percentiles in Power BI using DAX, covering the essential functions and practical, step-by-step examples you can use today.

What Are Percentiles and Why Use Them in Your Reports?

In simple terms, a percentile is a measure indicating the value below which a given percentage of observations in a group of observations falls. If your sales performance is in the 90th percentile, it means you performed better than 90% of your colleagues. It’s a powerful way to rank and compare data points within a dataset.

Unlike an average (or mean), percentiles are not easily distorted by outliers. One massive sale can dramatically inflate the average, but it will have a much smaller impact on the 90th percentile. This makes percentiles incredibly useful for:

• Performance-based ranking: Quickly identify your top-performing salespeople, marketing campaigns, or products.
• Setting thresholds: Define SLAs (Service Level Agreements) like "95% of support tickets must be resolved within 48 hours."
• Understanding distribution: See how evenly performance is spread across your team, instead of just looking at the average.

Understanding Power BI's Core Percentile Functions

Power BI offers two primary DAX functions to calculate percentiles: PERCENTILE.INC and PERCENTILE.EXC. While they sound similar, they have a key mathematical difference that's important to understand.

PERCENTILE.INC: The Inclusive Method

This is the most common and widely used percentile function. The "INC" stands for inclusive, meaning it includes the 0th and 100th percentiles in its calculation range. The 0th percentile is simply the minimum value in your dataset, and the 100th percentile is the maximum value.

Syntax:

measure = PERCENTILE.INC(<column>, <k>)

• <column>: The table and column containing your numbers (e.g., Sales[Revenue])
• <k>: The percentile you want to find, expressed as a decimal between 0 and 1 (e.g., 0.9 for the 90th percentile)

PERCENTILE.EXC: The Exclusive Method

The "EXC" stands for exclusive. This function calculates the percentile excluding the 0th and 100th percentiles from its range. Its mathematical range is strictly greater than 0 and less than 1.

Syntax:

measure = PERCENTILE.EXC(<column>, <k>)

Which One Should You Choose?

For 99% of business reporting scenarios—like analyzing sales data, marketing performance, or web traffic—you should use PERCENTILE.INC. It’s intuitive and aligns with how most people think about percentiles, covering the full range from the absolute lowest to the absolute highest value.

Use PERCENTILE.EXC only if you have a specific statistical reason to exclude the minimum and maximum values from your percentile ranking, which is very rare in standard business analytics.

How to Calculate a Specific Percentile (Using a Measure)

Let's find the 75th percentile for total sales from a sales data table. A measure is perfect for this, as it calculates a single, aggregate value that you can use in cards, charts, or other visuals.

Step 1: Get Your Data Ready

First, make sure you have a table in Power BI with a numerical column you want to analyze. For this example, let's assume we have a table named Sales with a column named Total Revenue.

Step 2: Create a New Measure

In the Report view, right-click on your Sales table in the Data pane on the right-hand side and select New measure. You can also click "New Measure" in the Home tab of the ribbon.

Step 3: Write Your DAX Formula

In the formula bar that appears at the top, type the following DAX formula. This will calculate the 75th percentile of the total revenue.

75th Percentile Revenue = PERCENTILE.INC(Sales[Total Revenue], 0.75)

Breaking it down:

• 75th Percentile Revenue: The name of our new measure.
• PERCENTILE.INC: The DAX function we are using.
• Sales[Total Revenue]: The column we are analyzing.
• 0.75: Represents the 75th percentile.

Once you've entered the formula, press Enter. Your new measure will appear in the Data pane, usually identified with a small calculator icon.

Step 4: Visualize Your New Measure

Now you can use this measure! The simplest way to see the result is with a "Card" visual.

• Click on an empty space on your report canvas.
• Select the Card visual from the Visualizations pane.
• Drag your new 75th Percentile Revenue measure into the Fields area of the card visual.

The card will now display the revenue value at the 75th percentile. This means 75% of all sales were below this amount, and 25% were above it.

Calculating a Percentile Rank for Each Row

Calculating a single value is great, but what if you want to rank every single salesperson or campaign to see which percentile they each fall into? This requires a different approach: a calculated column. A calculated column computes a value for each row in your table and stores it.

Our goal is to add a column called "Sales Percentile Rank" to our Sales table that shows the percentile for each salesperson's revenue.

Step 1: Create a New Calculated Column

Navigate to the Data view in Power BI (the icon that looks like a grid on the left pane). Select your Sales table. Then, from the table tools in the ribbon, click New Column.

Step 2: Write the DAX Formula for Percentile Rank

This DAX is a bit more advanced but incredibly powerful. In the formula bar, enter the following:

Percentile Rank = VAR CurrentRowRevenue = Sales[Total Revenue] VAR HigherOrEqualCount = COUNTROWS ( FILTER ( Sales, Sales[Total Revenue] >= CurrentRowRevenue ) ) VAR TotalRows = COUNTROWS ( Sales ) RETURN (TotalRows - HigherOrEqualCount) / (TotalRows - 1)

This can look intimidating, so let's break it down:

• VAR CurrentRowRevenue = Sales[Total Revenue]: First, we create a variable to store the revenue value of the current row being evaluated.
• VAR HigherOrEqualCount = ...: Next, we count how many rows in the entire Sales table have a revenue that is greater than or equal to the current row's revenue. We use FILTER to check every row.
• VAR TotalRows = COUNTROWS(Sales): We count the total number of rows (sales entries) in our table.
• RETURN ...: Finally, we apply the standard formula for percentile rank: we subtract the count of higher values from the total count and divide by the total count minus one. This gives us the percentage of entries that fall below the current row’s value.

Step 3: Format and Use Your New Column

After you press Enter, a new "Percentile Rank" column will appear. To make it more readable:

• Select the new column by clicking its header.
• In the Column tools tab that appears, change the Format to "Percentage."
• Adjust the number of decimal places if desired.

Now, when you switch back to the Report view, you can create a table visual and add the Salesperson, Total Revenue, and your new Percentile Rank column. You'll instantly see how each salesperson's performance ranks against the entire team.

Common Pitfalls and Best Practices

When working with percentiles in DAX, keep these tips in mind to avoid common frustrations:

• Measures vs. Calculated Columns: Use a measure (like PERCENTILE.INC) when you need a single, aggregate value for a visual (like a card or chart benchmark line). Use a calculated column when you need to calculate a percentile rank for every single row that you can use in table visuals or as a category in a slicer.
• Beware of Filter Context: Measures are affected by filters in your report. If you filter for a specific region, your percentile measure will recalculate just for that region's data. If you need the percentile of the entire dataset regardless of filters, wrap your table reference in an ALL() function (e.g., PERCENTILE.INC(ALL(Sales[Total Revenue]), 0.9)).
• Performance: Calculated columns add to your file size and increase data refresh times because their values are stored. Measures are calculated on the fly and are generally more efficient. For very large datasets, be mindful of how many complex calculated columns you add.

Final Thoughts

Understanding how to calculate and use percentiles moves your reporting from simple averages to a more detailed analysis of your data's distribution. Mastering the difference between getting a single percentile value with measures and creating row-level rankings with calculated columns gives you full control over your performance analysis inside Power BI.

While mastering DAX in Power BI is a powerful skill, it often comes after hours of troubleshooting complex formulas and learning data modeling best practices. We built Graphed to cut through that complexity entirely. Instead of writing formulas, you can connect your data sources (like Google Analytics, Shopify, and Salesforce) and just ask in plain English, "Create a table ranking my salespeople by revenue percentile for this quarter," and get an interactive, real-time dashboard in seconds. Our goal is to give you and your team direct access to the insights you need, without forcing you down the long path of becoming a business intelligence expert.