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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

95th Percent Value Not 95% of the value

Hi,

I have been trying to get this to work but I simply can't. What I am after is the 95th percent value. I have a table named events and the information in it is just like this. 

If i use the percentile.inc dax formula I  will get the value 31.35. What I want is the 95th% row value in this case the value would be 25. Calculated by ordering the values in order, then calculating the row at 95% (10*0.95) which is 10.45. The 10th row value is 25.

Anybody got any ideas how this can be done?


Panel GradingCompleted Time
57220DP48
57220DP425
57220DP422
57220DP433
57220DP415
57220DP416
57220DP419
57220DP420
57220DP423
57220DP45
57220DP410

 

Thanks in advance

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

Hi @Anonymous 

 

Okay, so a few quick steps should help you get to the outcome you're after. The approach I took isn't the best but it's hard to confirm the data types, etc., of each PBIX and all that.  So, to ensure you get what you are after, I created three Calculated Columns in the following order:

 

1_Rank = RANKX ( 'Table' , Table[Completed Time] , , ASC )
2_Percentile = INT ( COUNTROWS ( 'Table' ) * 0.95 )
3_Match = IF ( Table[1_Rank] = 'Table'[2_Percentile] , Table[Completed Time] , 0 )

The output is pretty on point 🙂

 

TheoC_2-1635317543971.png

I hope this is what you were after?  

 

All the best,

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

3 REPLIES 3
TheoC
Super User
Super User

Hi @Anonymous 

 

Okay, so a few quick steps should help you get to the outcome you're after. The approach I took isn't the best but it's hard to confirm the data types, etc., of each PBIX and all that.  So, to ensure you get what you are after, I created three Calculated Columns in the following order:

 

1_Rank = RANKX ( 'Table' , Table[Completed Time] , , ASC )
2_Percentile = INT ( COUNTROWS ( 'Table' ) * 0.95 )
3_Match = IF ( Table[1_Rank] = 'Table'[2_Percentile] , Table[Completed Time] , 0 )

The output is pretty on point 🙂

 

TheoC_2-1635317543971.png

I hope this is what you were after?  

 

All the best,

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

Thanks @TheoC  this really helped and worked initially. When I added more panels and timings I had to make some minor changes and I got it working

@Anonymous great to hear! And well done on the adjustments! 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors