How to create percentile (P90) in DAX: step by step

Calculating percentiles like P90 in DAX is useful to identify extreme values (for example response times, latency or sales) without being misled by the average. This guide shows how to calculate P90 in DAX in a filterable and correct way in a Power BI report.

Prerequisites

• Power BI Desktop with a simple data model (Fact and Dim tables).
• A fact table with the numeric metric (e.g.: TempoSegundos, ValorVenda).
• Basic knowledge of measures in DAX and use of visuals.

Step 1: Understand what a percentile is and why P90

A percentile (e.g.: P90) is the value below which 90% of the observations fall. P90 is useful to measure performance in the upper tail. In DAX we use functions like PERCENTILEX.INC or PERCENTILEX.EXC, or alternative techniques when the function is not available in your version.

Step 2: Simple measure with PERCENTILEX.INC

If your version of Power BI/Analysis Services includes PERCENTILEX.INC, this is the direct way. Suppose a table FactSales with the column ValorVenda. The measure calculates the P90 over the current filter context.

Percentil P90 =
PERCENTILEX.INC(
    ALLSELECTED(FactSales),
    FactSales[ValorVenda],
    0.9
)

Step 3: Make the percentile resilient to unwanted filters

Sometimes you want to respect dimension filters (e.g.: Product, Region) but ignore filters from other measures. Use ALLSELECTED or REMOVEFILTERS to control this. Example: respect selections of Product and Date, but ignore filters of CategoriaCustom:

Percentil P90 (controlado) =
VAR Base = ALLSELECTED(FactSales)
-- se quiser remover apenas um filtro específico: REMOVEFILTERS(DimCategoria)
RETURN
PERCENTILEX.INC(
    Base,
    FactSales[ValorVenda],
    0.9
)

Step 4: Alternative without PERCENTILEX (compatibility)

Not all versions have PERCENTILEX.INC. You can calculate an approximate P90 using MEDIANX on cumulative sets or create a percentile via cumulative distribution. Simple example that uses TOPN to reach the value that leaves 90% below — a discrete method:

Percentil P90 (sem PERCENTILEX) =
VAR T =
    ADDCOLUMNS(
        SUMMARIZE(ALLSELECTED(FactSales), FactSales[ValorVenda]),
        "CountVal", CALCULATE(COUNTROWS(FactSales))
    )
VAR Total = SUMX(T, [CountVal])
VAR Target = Total * 0.9
VAR Sorted =
    ADDCOLUMNS(
        T,
        "Cum", SUMX(
            FILTER(T, FactSales[ValorVenda] <= EARLIER(FactSales[ValorVenda])),
            [CountVal]
        )
    )
VAR Result = MINX(FILTER(Sorted, [Cum] >= Target), FactSales[ValorVenda])
RETURN
Result

Step 5: P90 by group (e.g.: by Product)

To calculate the percentile by group, put the measure in a visual with the dimension (e.g.: DimProduto[Nome]) — the row context will handle the group. If you want to force calculation by group regardless of other filters, use SUMMARIZE and EARLIER or GROUPBY. Example using PERCENTILEX.INC by product:

Percentil P90 por Produto =
PERCENTILEX.INC(
    VALUES(FactSales[IDVenda]),
    FactSales[ValorVenda],
    0.9
)
-- ou, mais robusto:
Percentil P90 por Produto (robusto) =
VAR ProdutoContext = VALUES(DimProduto[ProdutoID])
RETURN
PERCENTILEX.INC(
    CALCULATETABLE(FactSales, ProdutoContext),
    FactSales[ValorVenda],
    0.9
)

Verify the result

Validate P90: 1) Compare with manual calculation in Excel or Python for a small set; 2) Use a table visual in Power BI with the dimension and the P90 measure and check if values look correct (e.g.: 90% of the rows have ValorVenda <= P90). You can test by creating a card with the count of rows below the P90:

Linhas <= P90 =
VAR P = [Percentil P90]
RETURN
CALCULATE(COUNTROWS(FactSales), FactSales[ValorVenda] <= P)

Percentagem abaixo P90 =
DIVIDE([Linhas <= P90], CALCULATE(COUNTROWS(FactSales), ALLSELECTED(FactSales)))

Conclusion

Now you know how to calculate P90 in DAX with PERCENTILEX.INC and alternatives for compatibility, as well as how to control filters and calculate by group. Next steps: try P50/P95, compare PERCENTILEX.INC with discrete methods and use P90 in alerts or segmentation. Tip: always check the filter context — it is the most common cause of unexpected results.