QlikToDax

Fractile(Sales, 0.9)

Fractile (percentiles) in Power BI

Fractile() is inclusive and interpolates between values, which is the same method as DAX PERCENTILE.INC. FractileExc() matches PERCENTILE.EXC. For a percentile over an aggregated grain (Fractile with Aggr), use PERCENTILEX.INC over a table of groups.

Direct equivalent DAX Power Query

Short answer: Fractile(Sales, 0.9) becomes PERCENTILE.INC(Sales[Amount], 0.9). Fractile(Aggr(Sum(Sales), Customer), 0.9) becomes PERCENTILEX.INC(VALUES(Customer[CustomerID]), [Sales], 0.9).

What it does in Qlik

Fractile([{set}] [DISTINCT] [TOTAL [<fld>]] expr, fraction) returns the value at the given fraction (0 to 1) of the data, interpolating between the two closest values. It uses the inclusive method. FractileExc uses the exclusive method. Median(x) is the same as Fractile(x, 0.5).

Qlik example

Qlik chart expressions
Fractile(Sales, 0.9)
Fractile(Aggr(Sum(Sales), Customer), 0.75)
FractileExc(DeliveryDays, 0.95)

Power BI equivalent

DAX measures

DAX
P90 sales line = PERCENTILE.INC ( Sales[Amount], 0.9 )

P75 customer sales =
PERCENTILEX.INC ( VALUES ( Customer[CustomerID] ), [Sales], 0.75 )

P95 delivery days (exc) = PERCENTILE.EXC ( Orders[DeliveryDays], 0.95 )

Power Query

Power Query M
// Single value over a column (interpolated, like Fractile)
= List.Percentile(Source[Amount], 0.9, [PercentileMode = PercentileMode.ExcelInc])

List.Percentile already defaults to PercentileMode.ExcelInc, the Fractile method. Pass PercentileMode.ExcelExc for FractileExc.

Gotchas and behaviour differences

  • PERCENTILE.EXC returns an error when the fraction is too close to 0 or 1 for the number of rows. Fractile is more forgiving, so wrap EXC measures in IFERROR or use INC.
  • Qlik ignores NULLs and text. DAX PERCENTILE.INC on a column ignores BLANKs, but PERCENTILEX.INC includes a group when the measure returns 0, so filter out zero-sales groups if Qlik had them as NULL.
  • Fractile(DISTINCT ...) has no direct flag in DAX. Iterate over VALUES(column) with PERCENTILEX.INC to get the same result.

Official documentation

Related pages

Median(Sales), Mode(Product), Stdev(Sales) Median, Mode and Stdev Median and Stdev have direct DAX functions: MEDIAN/MEDIANX and STDEV.S/STDEVX.S (Qlik's Stdev is the sample version). Mode has no DAX function. Use TOPN over a grouped table, and decide what happens on ties, which Qlik answers with NULL. Partial / different approach Avg(Aggr(Sum(Sales), Customer)) Aggr (nested aggregation) Aggr builds a virtual table of aggregated values, for example sales per customer, so you can aggregate again. In DAX that is an iterator (AVERAGEX, MAXX, COUNTROWS(FILTER(...))) over VALUES or SUMMARIZE that calls a measure. Partial / different approach Sum(TOTAL Sales) / Sum(TOTAL <Category> Sales) The TOTAL qualifier Qlik's TOTAL qualifier makes an aggregation ignore the chart dimensions but keep the selections. In DAX that is ALLSELECTED. TOTAL <Field> keeps one dimension, which maps to removing only the other dimension columns. Direct equivalent

Spotted an error or a better pattern? QlikToDax is maintained by a working BI consultant; corrections are welcome via the About page. Last reviewed 2026-10-25.