Variable input to filtered percentile meassures - solved by writing this very post!

BooDaa — Fri, 31 Mar 2023 14:29:06 GMT

Hi community members!
I have been able to create some meassures based on fixed percentiles values like so

AverageAbove90thPercentile =

var limit = PERCENTILEX.EXC(ALL(Source),Source1[adjustedSlaFixTime], .9)

return

CALCULATE(AVERAGE(Source[adjustedSlaFixTime]),FILTER(Source,[adjustedSlaFixTime]>limit))

I have also created a set of variables that allow report consumers to choose a percentile from 75 to 95 via a slider.
Up to now I haven't been able to combine them, but while writing to ask for guidance the error struck me, like a two by four across the face. Over and over I have tried to push the percent figure 75-95 from my slider rather than the .75 to .9 used in the percentile calculation.

Error message reads Non supported percentile

When changing my COUNT, SUM or AVERAGE meassure variables from HigherPercentileParam to HigherPercentileLimit formula it worked like a charm. 😎

HigherPercentileParam = GENERATESERIES(75, 95, 5)

HighPercentileSelection = SELECTEDVALUE('HigherPercentileParam'[HigherPercentileParam], 0)

HigherPercentileLimit = HigherPercentileParam[HighPercentileSelection]/100

Based on this discovery my meassure was changed to:
AverageAboveVarPercentile =
var limit = PERCENTILEX.EXC(ALL(Source),Source[adjustedSlaFixTime], HigherPercentileParam[HighPercentileSelection]/100)
return
CALCULATE(AVERAGE(Source1[adjustedSlaFixTime]),FILTER(Source1,[adjustedSlaFixTime]>limit))

Please feel free to comment if you see any error in the meassures. I haven't validated it against a smaller dataset yet, so I'm not sure it is accurate.

Best regards,
Fredrik

topic Variable input to filtered percentile meassures - solved by writing this very post! in DAX Commands and Tips

Variable input to filtered percentile meassures - solved by writing this very post!