Forum Discussion
Percentile chart : Best way?
Hi,
Thanks for the response.
I tried the DAX queries and percentile function available in Power BI, but it returns single "k" value for the percentile we want. I dont know how to create a loop and store values so that I can run it on any data set and plot the graph.
I have attached sample data along. I need to create percentile chart for a data set like this where in I can see the values P50, P80 etc.
where in P values(0 to 100) would be plotted on y axis and corresponding values will be plotted on x axis.
Please suggest further.
| Value |
| 11399 |
| 12265 |
| 14187 |
| 11007 |
| 10157 |
| 10436 |
| 12392 |
| 13121 |
| 10134 |
| 14030 |
| 11284 |
| 12342 |
| 12162 |
| 11255 |
| 10496 |
| 13219 |
| 14138 |
| 13764 |
| 11029 |
| 11234 |
| 10153 |
| 12961 |
| 14217 |
| 10008 |
| 10751 |
| 12654 |
| 11026 |
| 12077 |
| 14786 |
| 12847 |
| 12825 |
| 10424 |
| 12060 |
| 12761 |
| 10040 |
| 13166 |
| 13175 |
| 14116 |
| 11384 |
| 12736 |
| 14083 |
| 13856 |
| 10310 |
| 10781 |
| 10284 |
| 14618 |
| 10279 |
| 12726 |
| 10171 |
| 13017 |
| 11188 |
| 10177 |
| 12820 |
| 11059 |
| 10698 |
| 12245 |
| 13277 |
| 14280 |
| 14743 |
| 13741 |
| 10983 |
| 11616 |
| 11587 |
| 11084 |
| 12339 |
| 14988 |
| 11131 |
| 12177 |
| 12449 |
| 11492 |
| 12042 |
| 17790 |
| 16370 |
| 16661 |
| 16279 |
| 17423 |
| 15365 |
| 10583 |
| 12508 |
| 10904 |
| 16944 |
| 18890 |
| 16083 |
| 19303 |
| 18711 |
| 14147 |
| 17115 |
| 16004 |
| 15230 |
| 15812 |
| 15711 |
| 15106 |
| 15092 |
| 10264 |
| 17185 |
| 10510 |
| 12026 |
| 18389 |
| 10738 |
| 17841 |
| 16604 |
| 17676 |
| 18571 |
| 17716 |
| 16433 |
| 12965 |
| 16413 |
| 13015 |
| 19635 |
| 11732 |
| 16203 |
| 19310 |
| 18054 |
| 19894 |
| 15966 |
| 10257 |
| 11757 |
| 13995 |
| 18047 |
| 17887 |
| 18635 |
| 18370 |
| 11474 |
| 14826 |
| 17062 |
| 17145 |
| 15683 |
| 12350 |
| 12549 |
| 15994 |
| 14448 |
| 15604 |
| 15010 |
| 13702 |
| 12267 |
| 10677 |
| 10551 |
| 15899 |
| 18344 |
| 10386 |
| 17967 |
| 17918 |
| 14517 |
| 19399 |
| 18953 |
| 16036 |
| 12476 |
| 10329 |
| 14871 |
| 19462 |
| 17718 |
| 15134 |
| 17243 |
| 16763 |
| 18113 |
| 14885 |
| 11234 |
| 17191 |
| 11449 |
| 18388 |
| 17232 |
| 19385 |
| 17308 |
| 14903 |
| 15566 |
| 10537 |
| 11796 |
| 18873 |
| 15147 |
| 18328 |
| 18905 |
| 11748 |
| 14047 |
| 11133 |
| 16149 |
| 17055 |
| 16579 |
| 12091 |
| 13203 |
| 15479 |
Microsoft EmployeeHi shreyyyyy,
I got below result referring to Simon-Hou's suggestion in above link. Does this meet your desired output?
If not, what is your expected result? Coud you post an image to illustrate? Also, what do you mean 'I dont know how to create a loop and store values so that I can run it on any data set and plot the graph.'?
Regards,
Yuliana Gu
There is an old pbix file which contains Percentile Chart:
http://blog.pragmaticworks.com/power-bi-custom-visuals-percentile-chart
Applying the same on the sample data which I shared above:Percentile chart
This is the desired output that I am looking for.
Looking for the same visual. Not sure how to plot my data :(
Hi shreyyyyy
Try the following
1. Load your data as RawData table
2. Using Edit Query sort the data from lowest to highest.
3. Add an Index column from 1
You RawData Table will have two columns Index and Value after the above steps.
4. Create a Table called Percentile with column name PIndex. This will have 100 records starting from 1 to 100.
5. Create a calculated column in this as
IndexNumber = ROUND( ('Percentile'[PIndex]/100)*Countrows(RawData),0)
What this does is finds the row in the RawData that corresponds to the %tile .
6. Create a calculated column
ActualValue= LOOKUPVALUE(RawData[Value ],RawData[Index],Percentile[IndexNumber])
This loads the actual value for the %tile from the RawData matching the index with the number found at step 5.
7. Now plot a line graph with ActualValue as x-Axis and P-Index as values . And set Pindex to sum.
You should get a chart like this.
Pbix file link https://drive.google.com/file/d/0B-CruXqyozMQdUI3eWhLaVFZSHc/view?usp=sharing
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Hey,
Thanks for the prompt response.
A spike can never come in a percentile chart, since for percentile calculation, the data is always sorted.
Also, it looks from first view that you have calculated the percentiles for each data point and then plotted it. Whereas, I am looking for a graph exactly similar to this:
https://www.youtube.com/watch?v=SwZh7jz_tgI&t=338s
So that I can see P50, P80 etc values for any data set.
