cancel
Showing results for
Search instead for
Did you mean:
pat_energetics Member

## Percentile K argument

Is there a way of using a measure result directly in the "K" argument of the percentile function?

Where in the following example; 'Band' is a 10 row table with columns [BandNumber] of values 1-10 and [K Perc] of values 0, 0.1, 0.25, 0.45, 0.5, 0.55, 0.75, 0.9, 0.95, 1

eg K Perc Result := IF(HASONEVALUE(Band[K Perc]),VALUES(Bands[K Perc]),0.5)

PercentileBandResult:= CALCULATE

(PERCENTILEX.INC(FactTable, FactTable[Column],[K Perc Result]), // other filters, //)

or should a VARiable be used within the measure to explicitly return a value to the percentile function e.g

VAR
KValue = IF(HASONEVALUE(Band[K Perc]),VALUES(Band[K Perc]),

SWITCH (Band[K Perc],

"0" = 0,

"0.1" = 0.1,

etc,

etc,

"1" = 1,

//if all the above fail //,0.5)
RETURN

PercentileBandResult:= CALCULATE

(PERCENTILEX.INC(FactTable, FactTable[Column],KValue, // other filters, //)

2 REPLIES 2
pat_energetics Member

## Re: Percentile K argument

This one has been solved using the VAR technique

VAR

K_Perc = IF(HASONEVALUE(Band[K Perc]),VALUES(Bands[K Perc]),0.5)

RETURN

PercentileBandResult:= CALCULATE

(PERCENTILEX.INC(FactTable, FactTable[Column],K_Perc), // other filters, //)

This allows percentile / frequency distribution analysis to be done by loading pre-defined values for K in a disconnected table reference -  (Band[K Perc]) in this case. Really useful if you want to view standard P0, P10, P25, P50, P75, P90 and P100 values for a range of distributed data sets.

prerak Frequent Visitor

## Re: Percentile K argument

Hey, So I've tried to get this to work, though when I apply my measure in such a way

```M_TrialToPaid_PercK =
TOTALMTD
(
PERCENTILE.INC('tbl_name'[delta_purchase],
[M_PercK]
),
dates[actual_created],
'tbl_name'[purchase_type]="purchase_trial"
)```

and

`[M_PercK] =  IF(HASONEVALUE(bands[k]), VALUES(bands[k]), 0.5)`

where column k = [0.0, 0.05. 0.1, 0.2 .... 0.9, 0.95]

- In other words, I'm trying to evaluate my monthly percentiles(p0, p5, p10 ... p90, p95) on the [delta_purchase] column.

-- But it just returns me the median. -- What I need is all the percentiles(p0, p5, p10 ... p90, p95) seperated by month.