PercentRank below a fixed value

[mpheelee]

Hello,

I'm looking for a formula that will allow me to identify the % turnaround times for a CT Scan that are below a fixed time such as 120 minutes.  Percentrank.inc(array, 120) works in excel.  

Has anyone found an equivalent in Power BI?

Thanks!

[v-sathmakuri]

Hi @mpheelee ,

I hope this information proves helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @mpheelee ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sathmakuri]

Hi @mpheelee , 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thanks!!

[v-aatheeque]

Hi @mpheelee ,
Thanks for reaching out to Microsoft Fabric Community Forum.
In Power BI, you can use DAX to calculate the percentage of turnaround times below a fixed value like 120 minutes. 
DAX: 
CT Scan % Turnaround Rank = 
VAR CurrentValue = MAX('CT Scans'[Turnaround Time])
RETURN
1 - DIVIDE(
    RANKX(ALL('CT Scans'), 'CT Scans'[Turnaround Time], , ASC),
    COUNTROWS(ALL('CT Scans'))
)

If you still persists the issue @Ashish_Mathur  please share the sample data for more insights.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[mpheelee]

Here is the data.  Normally there are 30k+ rows.  The percent.rank.inc for the below data is 86.3%

% rank under 2 hours (120 min)

0.863

Formula =PERCENTRANK.INC(B7:B221,120,3)

The next issue is the real reason I am trying to use Powerbi.  I need to be able to calculate this number by month using a filter in bi.  I can do it in excel, but would rather be able to simply add the next month's data to my table.

TAT minutes
124
124
123
123
123
122
122
122
122
122
122
122
122
121
121
121
121
121
121
121
120
120
120
120
120
120
120
120
120
120
119
119
119
119
119
119
119
119
119
119
118
118
118
118
118
118
118
118
118
118
118
118
117
117
117
117
116
116
116
116
116
116
116
116
116
116
115
115
115
115
115
114
114
114
114
114
114
114
114
114
113
113
113
113
113
113
113
113
113
113
113
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
111
111
111
111
111
111
111
111
111
110
110
110
110
110
110
110
110
110
110
110
110
109
109
109
109
109
109
109
109
109
109
108
108
108
108
108
108
108
108
108
108
108
107
107
107
107
107
107
107
107
107
107
107
106
106
106
106
106
106
106
106
106
106
106
106
106
106
106
106
105
105
105
105
105
105
105
105
105
105
105
105
105
105
105
105
104
104
104
104
103
103
103
103
103
103
103
103
103
103
103
103
103
103
102
102

[Ashish_Mathur]

I just typed this in Google - percentrankinc function in powerbi.

Could you check on your own