cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

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
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. Man Sad

-- What I need is all the percentiles(p0, p5, p10 ... p90, p95) seperated by month.