dax find 75/95/99 percentile and return value

MattReckoner — Tue, 25 Apr 2023 19:10:12 GMT

i have an issue with Percentile/inc and exc calculations.

My fact table contains of ProductName, ProjectName and Timing(seconds)

i need to find 75th 90th 99th timing percentiles for each ProductName. The problem with Percentile.INC/EXC formula is with odd combinations. For example i have 183 rows of 9029T ProductName for One ProjectName, the 99th percentile is 181,17 sorted row number. Dax percentile function returns me calculated Timing(seconds) somehow ine between 181 and 182 row. But i would need to round odds and return Timings(seconds) matching exactly a value from 181 row

How could i do it?

so far i made a measure

MsrRowNo99th=CALCULATE(ROUND(COUNTROWS(FtblProd)-1)*99/100+1,0))

it returns me the row number from sorted table i would need to return

calculatedColumn

CCRankTiming=

var ProductNames = FtblProd[ProductName]

var ProjectNames = FtblProd[ProjectName]

return

RANKX(Filter(FtblProd,FtblProd[ProductName]=ProductNames && FtblProd[ProjectName]=ProjectNames),FtblProd[Timing(seconds)],,ASC))

checked it and it shows me good ranking for my Timing

and as a third step added my last measure

MsrReturn99thT=MAXX(FILTER(FtblProd),CCRankTiming=MsrRowNo99th),FtblProd[Timing(seconds)])

and that one return me an error...maybe instead of filter i should pass virtual table with productname and projectname

Could anyone help? maybe all these steps can be done within one measure for each percentile?

Re: dax find 75/95/99 percentile and return value

some_bih — Wed, 31 May 2023 12:07:35 GMT

Hi MattReckoner check link with video for Percentile function in DAX. I hope this help.

https://dax.guide/percentile-exc/

topic dax find 75/95/99 percentile and return value in DAX Commands and Tips

dax find 75/95/99 percentile and return value

Re: dax find 75/95/99 percentile and return value