cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Percentile chart : Best way?

Hi

There was percentile chart available in Power BI earlier:

https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-percentile-chart/

But this was removed for IDK reasons. Now I want to create the same chart for visualizing the distribution of certain numerical data.

What is the best way to achieve that?

I tried writing custom logic where I calculated percentile for each and every data point(after sorting and indexing) and then plot it against the data.  (Too much calculation and processing)

Also, when I want to filter this data and see percentile distribution of the selected sample, it gives wrong output (Since for example:  Data was for all the cities of USA and if I want to drill down and see it for a particular state, then indexing and sorting will need to be done again)

Please suggest the best possible way to generate a percentile chart in Power BI

Or if I can import the same from anywhere?

7 REPLIES 7
Microsoft

## Re: Percentile chart : Best way?

Hi @shreyyyyy,

Currently, percentile chart is not an available custom visual that can be downloaded from Office Store, but you could make the same percentile calculation via DAX, please refer to below links for details:

PERCENTRANK (Inclusive)

Create a dynamic BI distribution Chart in PowerPivot using DAX

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: 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.

 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

## Re: Percentile chart : Best way?

Hi @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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Percentile chart : Best way?

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:

So that I can see P50, P80 etc values for any data set.

Frequent Visitor

## Re: Percentile chart : Best way?

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.

Frequent Visitor

## Re: Percentile chart : Best way?

Looking for the same visual. Not sure how to plot my data 😞

Super User I

## Re: Percentile chart : Best way?

Try the following

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.

If this works for you please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors