Forum Discussion

mpheelee's avatar
mpheelee
Frequent Visitor
1 year ago

PercentRank below a fixed value

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!

Need Help

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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's avatar
      mpheelee
      Frequent Visitor
      1 year ago

      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's avatar
        Ashish_Mathur
        Super User
        1 year ago

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

        Could you check on your own

         

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    1 year ago

    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!


  • v-sathmakuri's avatar
    v-sathmakuri
    Community Support
    1 year ago

    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-sathmakuri's avatar
    v-sathmakuri
    Community Support
    1 year ago

    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's avatar
    v-sathmakuri
    Community Support
    1 year ago

    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!!