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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MacyMolly
Helper II
Helper II

PERCENTRANK.INC IN DAX

Hi Everyone, Need your help on this. How to create dax codes equivalent to percentrank.inc. Kindly refer to the image, desired output that i'm trying to achieve in dax. Any help will be much appreciated.

 

MacyMolly_1-1696506965698.png

 

5 REPLIES 5
_elbpower
Resolver III
Resolver III

You can use the RANKX and COUNTROWS functions. Here's how you can create a DAX measure equivalent to PERCENTRANK.INC:

 

PercentRankInc = 
VAR CurrentValue = [YourValueToRank]
RETURN
DIVIDE(
    COUNTROWS(FILTER(ALL('YourTable'), 'YourTable'[YourColumn] <= CurrentValue)),
    COUNTROWS(ALL('YourTable')),
    0
)

 

Replace the following placeholders:

  • [YourValueToRank] with the value you want to rank.
  • 'YourTable' with the name of your table.
  • 'YourColumn' with the name of the column containing the data you want to rank.

This DAX measure calculates the rank of the CurrentValue in the column specified by 'YourColumn' as a percentage of the entire data set in 'YourTable'. It uses the FILTER function to count the number of rows with values less than or equal to CurrentValue, and then divides this count by the total number of rows in the table to get the percentile rank.

@_elbpower , 
i'm trying to achieve this percentrank formula:
PERCENTRANK = lowernumber/(lowernumber + uppernumber)

Can you please define what is lowernumber and uppernumber

@_elbpower ,

If you refer to the below image, in column income_household_median, if I want to get the percentrank of each value, I need to compare each value from the rest. For example, the first value is 39988, i'm going to compare 39988 from the rest of the values and get the count how many of them are lesser(lowernumber) from 39988, and getting as well the count of greater than (uppernumber) 39988. From there, I can compute the percentrank which is lowernumber/(lowernumber+uppernumber). 

 

MacyMolly_0-1696593067975.png

 

Here's a rewritten version of your DAX measure with some added comments for clarity:

 

PercentRankInc =
VAR CurrentValue = [income_household_median] // Get the current value to rank
RETURN
    DIVIDE(
        COUNTROWS(
            FILTER(
                ALL('YourTable'), // Consider all rows in the table
                'YourTable'[income_household_median] < CurrentValue // Filter rows where the value is less to the current value
            )
        ),
        COUNTROWS(ALL('YourTable')), // Calculate the total number of rows in the table
        0 // Handle division by zero (to avoid errors)
    )

 

This measure effectively computes the percentile rank for each value in the "income_household_median" column, as you described.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors