Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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, //)
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |