Forum Discussion
Problem with Percentiles
Hi
I have been asked to build a report which looks at 90th percentile of page load times of our website.
At the moment I have this done simply where in the SQL that runs in the back end it uses an ntile function in a subquery
ntile(100) over (partition by `date` order by loadtime as percentile
of which I then calculate
select
`date`,
min(case when percentile >= 90 then loadtime else null end) as 90ptile .....
This means by date I can see the 90th percentile. But I want to see the correct totals and add a number of other dimensions so a user can use slicers as they want and have the numbers re-calculate. I can add as many dimensions as I want to the ntile function/output but that will only allow me to report on one level of detail (e.g date, browser or date+browser). Power Bi won't be able to do anything other than show the data for that same level of detail.
I am aware of percentile dax functions but I believe I would need to import every page view datapoint which would be too large even for one day.
Is there another way I could do this?
4 Replies
Take a look at the dynamic abc classification pattern. I think this conceptually will do what you want with a bit of simple modification. https://www.daxpatterns.com/abc-classification-dynamic/
Probably i'm not getting my head round that but I don't see how it would solve my problem.
What would the data I bring into Power Bi look like?
e.g. at the moment it's something like
date 90th percentile
01/01/2019 100and i'm wanting to have multiple dimensions in addition to date but I must need a different column(s) to 90th percentile so power Bi can do the calculation (much like for average you'd have 2 sums instead of a pre-calculated average field).
I suggest you post some sample data and explain the problem as it relates to the sample.
