Percentile Column with group by

[AlanPhan]

Hello community,

I have one table as below:

Col1 Col2 Value Percentile
Test John  100     0.0
Test John  200     0.0
Test John  300     0.0
Test John  100     0.1
Test John  200     0.1
Test John  300     0.1
Test John  100     1.00
Test John  200     1.00
Test John  300     1.00

Percentile column is the range to calculate percentile with range from 0.0 to 1.0

How to calculate percentile based on two column 'Value' and 'Percentile' and group by Col1, Col2, Percentile.
I want to have new table like this:

Col1 Col2 Percentile   Percentile_Value
Test John  0.0          100
Test John  0.1          100
Test John  1.0          300

Any helps will be appriciated.

2/ Or with this table:

Col1 Col2 Value
Test John  100
Test John  200
Test John  300

How can I calculate Percentile (group by Col1, Col2) with range from 0 to 1 (0.0, 0.1, 0.2.... 0.98, 0.99, 1.00)?

[v-xinruzhu-msft]

Hi @AlanPhan ,

 ^{Maybe you can create a new measure in the table , the code is as follows:}

Measure 2 = CALCULATE(PERCENTILE.INC('Table'[value],SELECTEDVALUE('Table'[Percentile])),'Table'[Percentile]=SELECTEDVALUE('Table'[Percentile]),ALL('Table'))

))

^{Then add a ‘Table’  visual and put ‘col1’ ‘col2’ ‘percentile’ fields and measure2 to the table, the output is as follows:}

^{This is the median I calculated for each group, whether it meets your needs, I found that the median calculated by the second group is different from the result in the picture you provided, could you provide the calculation logic?}

 If your Current Period does not refer to this, please clarify in a follow-up reply.

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[AlanPhan]

hi @v-xinruzhu-msft ,

I want to have a table as above, with each row will have 100 point (range from 0 to 1) for 'Value' array, so that I can show on the chart.

With your result, how can I show to line chart with enough 100 point?

[v-xinruzhu-msft]

Hi @AlanPhan 

What you describe is a bit abstract, is there a similar graphic description?

^{Best Regards,}

^{Yolo Zhu}

[AlanPhan]

@v-xinruzhu-msft , 

You can see the line chart as below, I want to have 100 points for each percentile (range from 0 to 1). Is it possible?

[NikhilChenna]

AlanPhan, i think you should create a rank column based on value and percentile column and filter the data accordingly. 

Regards,
Nikhil Chenna 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[AlanPhan]

could you have a sample for this? I tried to create 2 table (Percentile with Value column, data from 0 to 1), and table need to be calculated. But it seems difficult to implement.

[NikhilChenna]

Hi @AlanPhan , right now i do not have an example to show you. 
But you can check for Rankeq funtion.

Regards,
Nikhil Chenna 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[FreemanZ]

don't understand the percentile here, could you explain the calculation behind?

[AlanPhan]

hi @FreemanZ , 
I updated post again. Also, please see the note 2/ that I mentioned below. It's easy to understand. 
Please advise.