Forum Discussion

pat_energetics's avatar
pat_energetics
Advocate II
10 years ago

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

  • pat_energetics's avatar
    pat_energetics
    Advocate II
    10 years ago

    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's avatar
    prerak
    Advocate I
    9 years ago

    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. :mansad:

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