Forum Discussion
Problem with Percentiles
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).
I suggest you post some sample data and explain the problem as it relates to the sample.
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.