Problem with Percentiles

[Troops]

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?

[MattAllington]

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/

[Troops]

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        100

and 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).

[MattAllington]

I suggest you post some sample data and explain the problem as it relates to the sample.

[Troops]

Sure, but the main issue is not being able to get the data into a format I want (so it's probably not a Power Bi question but was hoping someone would have a suggestion).

Some sample data would be

Date            Device      Average Time    90th percentile time

01/01/2019 Desktop     100                     150

01/01/2019 Mobile       200                      240

02/01/2019 Desktop     120                      165

02/01/2019 Mobile        220                      270

Now if I were to create some graphs of average time by date ignoring device this wouldn't work because i'd be using an average of an average which would be wrong.

However for average I can approach it differently by instead of importing the average field, importing two sum columns (total pageviews and total time) and creating a average measure in Power Bi (sum(time)/sum(pageviews))

Date            Device      Pageviews Total Time    90th percentile time

01/01/2019 Desktop     10000          1000000        150

01/01/2019 Mobile       2000             400000         240

02/01/2019 Desktop     12000          14400000     165

02/01/2019 Mobile        2200            484000        270

This way works no matter how many dimensions I have (e.g. browser, specific page).

However I can't figure out a way I can do this for 90th percentile as it isn't a calculation as simple as using two sum columns. It has to partitoned the data by date/device then for each partition order the data by ascending time, percentile each rank and find the closest to 90th percentile.