topic Re: Percentile Column with group by in DAX Commands and Tips

Percentile Column with group by

AlanPhan — Thu, 24 Nov 2022 09:40:13 GMT

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)?

Re: Percentile Column with group by

FreemanZ — Thu, 24 Nov 2022 09:24:44 GMT

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

Re: Percentile Column with group by

AlanPhan — Thu, 24 Nov 2022 09:35:13 GMT

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

Re: Percentile Column with group by

NikhilChenna — Thu, 24 Nov 2022 09:52:32 GMT

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!

Re: Percentile Column with group by

AlanPhan — Thu, 24 Nov 2022 10:01:13 GMT

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.

Re: Percentile Column with group by

NikhilChenna — Thu, 24 Nov 2022 10:12:35 GMT

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!

Re: Percentile Column with group by

Anonymous — Fri, 25 Nov 2022 02:53:33 GMT

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.}

Re: Percentile Column with group by

AlanPhan — Fri, 25 Nov 2022 03:22:56 GMT

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?

Re: Percentile Column with group by

Anonymous — Fri, 25 Nov 2022 05:43:57 GMT

Hi AlanPhan

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

^{Best Regards,}

^{Yolo Zhu}

Re: Percentile Column with group by

AlanPhan — Mon, 28 Nov 2022 15:35:15 GMT

Anonymous ,

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?