cancel
Showing results 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

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.

5 REPLIES 5
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.

Helper II

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

Resolver III

Can you please define what is lowernumber and uppernumber

Helper II

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

Resolver III

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

Announcements

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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