topic Re: Solved: Showing Percentile of group in column in DAX Commands and Tips

Solved: Showing Percentile of group in column

PowerBIUserX — Thu, 24 Nov 2022 08:22:07 GMT

Hi all,

I have the following table:

- First 3 columns are data,

- Percentile columns are what I'm trying to calculate. The calculation below is the median for the corresponding group - but I would like to be able to replicate for any percentile.

- The last column is the end goal, comparing if a data point is below, same, or above the percentile of the group.

How can I create the percentile and comparison columns in PowerBI, using metrics/columns. Do I need to create a calculated table?

Thanks in advance!

Re: Solved: Showing Percentile of group in column

amitchandak — Wed, 23 Nov 2022 09:20:30 GMT

PowerBIUserX , is it Percentile or Avg

like a new column

Avg Group 1 = averagex(Filter(Table,[Group 1] = earlier([Group1]) ), [Value])

Avg Group 2 = averagex(Filter(Table,[Group 2] = earlier([Group2]) ), [Value])

Then you can diff in new columns

Re: Solved: Showing Percentile of group in column

Anonymous — Thu, 24 Nov 2022 06:06:56 GMT

Hi PowerBIUserX ,

^{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: Solved: Showing Percentile of group in column

PowerBIUserX — Thu, 24 Nov 2022 07:56:43 GMT

Hi,

Thank you, this is indeed the calculation I'm looking for. I've updated the table to reflect the correct numbers.

What is the calculations/DAX you used?

Best regards,

Re: Solved: Showing Percentile of group in column

Anonymous — Thu, 24 Nov 2022 08:19:41 GMT

Hi PowerBIUserX ,

I create two measeures,the codes are as follows,

Me-group1 = CALCULATE(MEDIAN('Table'[Value]),'Table'[Group1]=SELECTEDVALUE('Table'[Group1]),ALL('Table'))

Me-group2 = CALCULATE(median('Table'[Value]),'Table'[Group2]=SELECTEDVALUE('Table'[Group2]),ALL('Table'))

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: Solved: Showing Percentile of group in column

PowerBIUserX — Thu, 24 Nov 2022 08:21:28 GMT

Thank you so much!!