How to create a percentile ranking column in Power BI

João Barros 08 de November de 2026 4 min read

This tutorial shows how to create a column that ranks observations into percentiles (for example, deciles or custom percentiles) in Power BI using DAX. Percentile ranking is useful to segment customers, values, or metrics and prepare comparative analyses.

Prerequisites

Power BI Desktop installed.
Model with a fact/value table (e.g.: Sales, with field Amount or Value) loaded.
Basic DAX knowledge (measures versus columns).

Step 1: Choose the percentile logic (continuous rank vs buckets)

Before writing DAX, decide whether you want a continuous percentile (0-100) or buckets (e.g.: decile 1..10). Buckets are easier for filters and visuals. Here we will create both: first the normalized continuous percentile and then the decile bucket.

Step 2: Create the measure or column for relative Rank

If you need the percentile to be calculated by filter context or by group (e.g.: by region), use measures. To create a persistent row-level column (independent of filters), create a calculated column. First example: column with continuous Percentile Rank between 0 and 100.

// Substitui 'Sales' e 'Amount' pelos nomes da tua tabela e coluna
PercentileRank (coluna) =
VAR ThisValue = Sales[Amount]
VAR RowCountLess =
    COUNTROWS(
        FILTER(
            ALL(Sales),
            Sales[Amount] < ThisValue
        )
    )
VAR RowCountEqual =
    COUNTROWS(
        FILTER(
            ALL(Sales),
            Sales[Amount] = ThisValue
        )
    )
VAR TotalRows = COUNTROWS(ALL(Sales))
// Usamos método de interpolação para valores iguais: rank médio
VAR RankPos = RowCountLess + (RowCountEqual / 2)
RETURN
IF(TotalRows = 0, BLANK(), (RankPos - 1) / (TotalRows - 1) * 100)

Explanation: we use ALL(Sales) to ignore filters and compare with the whole set. RowCountLess counts how many have a smaller value; RowCountEqual counts how many are equal. RankPos is based on the mid-rank method. The result is normalized to 0-100.

Step 3: Create buckets (e.g.: deciles) with SWITCH or INT

To make analysis easier, convert the continuous percentile into buckets (deciles 1..10). Use the previous column or calculate directly in DAX:

DecileBucket =
VAR P = Sales[PercentileRank (coluna)]
RETURN
SWITCH(
    TRUE(),
    P <= 10, 1,
    P <= 20, 2,
    P <= 30, 3,
    P <= 40, 4,
    P <= 50, 5,
    P <= 60, 6,
    P <= 70, 7,
    P <= 80, 8,
    P <= 90, 9,
    10
)

Compact alternative with INT (very useful for regular percentiles):

DecileBucketCompact =
VAR P = Sales[PercentileRank (coluna)]
RETURN
IF(ISBLANK(P), BLANK(), INT((P - 0.000001) / 10) + 1)

We adjust by 0.000001 to ensure that 10.0 falls into deciles 1..10 correctly without ambiguous edges.

Step 4: Calculate percentile by group (e.g.: by Region)

When you want percentiles within each group (Region, Category), replace ALL(Sales) with ALL(Sales[GroupColumn]) to keep comparison only within the group. Example by Region:

PercentileRank_ByRegion =
VAR ThisValue = Sales[Amount]
VAR ThisRegion = Sales[Region]
VAR RowCountLess =
    COUNTROWS(
        FILTER(
            ALL(Sales),
            Sales[Region] = ThisRegion && Sales[Amount] < ThisValue
        )
    )
VAR RowCountEqual =
    COUNTROWS(
        FILTER(
            ALL(Sales),
            Sales[Region] = ThisRegion && Sales[Amount] = ThisValue
        )
    )
VAR TotalRows = COUNTROWS(FILTER(ALL(Sales), Sales[Region] = ThisRegion))
VAR RankPos = RowCountLess + (RowCountEqual / 2)
RETURN
IF(TotalRows = 0, BLANK(), (RankPos - 1) / (TotalRows - 1) * 100)

Note: using ALL(Sales) with a condition keeps the scale only within the group. If your model has a Region table related, you may prefer to use RELATED or calculate in the appropriate context.

Step 5: Avoid common errors and optimize

Common errors: divisions by zero (no rows), duplicates that create identical percentiles, and performance on large tables. To improve performance, consider creating an aggregated ranking table or using RANKX with ALL or ALLSELECTED where it makes sense.

// Exemplo alternativo usando RANKX (pode ser mais simples mas menos controlável para empates)
Percentile_RANKX =
VAR Total = COUNTROWS(ALL(Sales))
VAR RankVal = RANKX(ALL(Sales), Sales[Amount], , ASC, DENSE)
RETURN
IF(Total = 0, BLANK(), (RankVal - 1) / (Total - 1) * 100)

RANKX can be sufficient for many scenarios; choose between DENSE or SKIP depending on how you want to handle ties.

Verify the result

Create a table visual in Power BI with columns: identifier, Amount, PercentileRank (coluna) and DecileBucket. Sort by Amount. Verify that percentiles increase monotonically and that approximately 10% of rows fall into each decile (if you have many unique values). Test with filters by Region to confirm percentiles by group.

Conclusion

Creating percentiles in Power BI with DAX allows you to segment data for analysis and reporting. You can expand to custom quantiles (tertiles, quintiles) by adjusting bucket boundaries, or turn these columns into slicers and legend fields in charts. Tip: always validate with aggregates (COUNT per bucket) to confirm the distribution — do you want to explore percentiles by customer or by region first?