Calculate percentile from measure

[Anonymous]

Hi, i want to calculate percentile based on the measure that i have created. Based on my knowledge to calculate percentile it needed to be from the column. May i know how to transfer the measure into a column ? Below is the sample table, A/B is the measure that came from column A and B. Thank you for your help.

Year	A	B	A/B
January	9	3	3
Feb	6	3	2
March	8	4	2

[VikasKarnekanti]

_max_y__axis =
VAR TableCalculated = SUMMARIZE(TableName, TableName[A], TableName[B], "A/B", DIVIDE(SELECTEDVALUE(TableName[A]), SELECTEDVALUE(TableName[B])))

RETURN PERCENTILE.INC([A/B], 0.8)

When using this dax i want from min to 0.2 , 0.2 to 0.4,0.4 to 0.6 , 0.6 to 0.8 and 0.8 to 1 percentile values and i want to combine values min to 0.2 , 0.2 to 0.4 ,0.4 to 0.6 ,0.6 to 0.8 , 0.8 to 1.0 

Ex : 1-20 , 21-40 , 41-60 , 61-80 , 81-100

Please explain the Measure how to write i mentioned as above.

 
Thanks in advance,
Vikas.K

[joaoribeiro]

Hi @Anonymous , can you share the expected output for the percentile calculation, considering the example that you shared?

For percentile, we have the PERCENTILE.EXC function in DAX that might help you, please find more detail in this link: https://learn.microsoft.com/en-us/dax/percentile-exc-function-dax

Also, you don't need to transform the measure in a column to use it in another measure, if that's your question.

If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[Anonymous]

_max_y__axis = MROUND(CALCULATE(PERCENTILE.EXC('Table'[A/B], 0.8) . This is my formula ,However, when I apply the formula, I do not get the option of calculating the percentile for measure.

[joaoribeiro]

Hi @Anonymous , please consider using the PERCENTILE.INC() in this case, as the PERCENTILE.EXC() will return an error if there is no value in the percentile desired range.

_max_y__axis = PERCENTILE.INC('TableName'[A/B], 0.8)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[Anonymous]

still the same , i can only select from the table's column , not the measure. the option that i get 'Table'[A] , 'Table'[B] but no 'Table'[A/B]

[joaoribeiro · Accepted solution]

@Anonymous , in this case I suggest you create a Virtual Table with "SUMMARIZE" to create the measure, and then use the PERCENTILE function on top of that. Please check the code below considering that the [A/B] column is A divided by B:

_max_y__axis = 
VAR TableCalculated = SUMMARIZE(TableName, TableName[A], TableName[B], "A/B", DIVIDE(SELECTEDVALUE(TableName[A]), SELECTEDVALUE(TableName[B])))

RETURN PERCENTILE.INC([A/B], 0.8)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro