Forum Discussion
Percentile Column with group by
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)?
9 Replies
FreemanZSuper User
don't understand the percentile here, could you explain the calculation behind?
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.
hi Anonymous ,
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?
Hi AlanPhan
What you describe is a bit abstract, is there a similar graphic description?
Best Regards,
Yolo Zhu
- NikhilChenna
Skilled Sharer
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!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!
