Hi
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?
