Forum Discussion
Percentile chart : Best way?
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
