求助：按类别实现DAX中等价于Excel PERCENTRANK.INC的计算方法

No problem! To replicate Excel's PERCENTRANK.INC function in DAX while grouping by your Category column, you'll need to use context-aware filtering to calculate the rank relative to only the values in the same category. Here's a step-by-step solution:

Step 1: Understand the PERCENTRANK.INC Formula #

Excel's PERCENTRANK.INC uses this logic for a value x in a dataset:

(number of values < x + (number of values = x - 1)/2) / (total values - 1)

We'll translate this into DAX, ensuring we only consider values within the current row's category.

Step 2: Create a Calculated Column #

Add a calculated column to your table using this DAX code (replace YourTableName with the actual name of your table from the Power Query output):

PercentRank_INC = 
VAR CurrentAmount = [Amount]
VAR CurrentCategory = [Category]
-- Filter the table to only include rows from the same category
VAR CategoryValues = FILTER('YourTableName', 'YourTableName'[Category] = CurrentCategory)
-- Total number of values in the category
VAR TotalCount = COUNTROWS(CategoryValues)
-- Number of values in the category less than the current amount
VAR ValuesLessThan = COUNTROWS(FILTER(CategoryValues, 'YourTableName'[Amount] < CurrentAmount))
-- Number of values in the category equal to the current amount
VAR ValuesEqualTo = COUNTROWS(FILTER(CategoryValues, 'YourTableName'[Amount] = CurrentAmount))
RETURN
-- Handle edge case where only one value exists in the category (avoid division by zero)
IF(
    TotalCount = 1,
    0,
    (ValuesLessThan + (ValuesEqualTo - 1)/2) / (TotalCount - 1)
)

How This Works #

• Context Filtering: The CategoryValues variable narrows down the dataset to only rows matching the current row's Category, so all calculations are category-specific.
• Edge Case Handling: If a category has only one value, we return 0 (since there's no other value to compare against, avoiding division by zero).
• Duplicate Values: For duplicate amounts, the formula uses the midpoint of their rank range, which matches Excel's PERCENTRANK.INC behavior exactly.

Example Output #

For a category with amounts [50, 150, 150, 250]:

• 50 → 0.0 (smallest value)
• 150 → (1 + (2-1)/2)/(4-1) = 1.5/3 = 0.5
• 150 → same as above (0.5)
• 250 → 1.0 (largest value)

This matches exactly what Excel would return for the same dataset.

内容的提问来源于stack exchange，提问作者Przemyslaw Remin