Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DinPagoh
Frequent Visitor

Calculate percentile from measure

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.



YearABA/B
January933
Feb632
March842
1 ACCEPTED SOLUTION

@DinPagoh , 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

View solution in original post

6 REPLIES 6
VikasKarnekanti
Regular Visitor

_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
Kudo Kingpin
Kudo Kingpin

Hi @DinPagoh , 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

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

Hi @DinPagoh , 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)

 

joaoribeiro_0-1681291732721.png

 

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

 

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]

@DinPagoh , 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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.