Forum Discussion
Problem with Percentiles
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.