OLAP环境下基于DAX度量的动态排名需求（替代求和排名）

Got it, let's tackle this ranking problem step by step. Since we can't create calculated columns due to OLAP restrictions in the pivot table, we'll build a measure-based ranking that prioritizes the 80% target first, then sorts by your rate measure in descending order.

Core Logic #

Our goal is to split items into two groups:

1. Items that meet or exceed the 80% target (these get top priority)
2. Items that fall below 80% (ranked by how close they are to the target, i.e., higher rate first)

Then, within each group, we sort the rate measure from highest to lowest. This will give you the ideal order: A, B, C, D, F, H, E, G.

DAX Measure Code #

First, make sure you have your existing rate measure (let's call it [Sum of Average Rate] for reference). Then create this new ranking measure:

Target-Based Rank = 
VAR TargetValue = 0.8
VAR CurrentRate = [Sum of Average Rate]
VAR PriorityGroup = IF(CurrentRate >= TargetValue, 2, 1) // Higher number = higher priority group
RETURN
RANKX(
    ALLSELECTED('YourTable'[ItemCategory]), // Replace with your column containing A/B/C/D/F/H/E/G
    PriorityGroup * 1000 + CurrentRate, // Weight group to ensure target-meeting items come first
    ,
    ,
    DESC, // Sort combined value in descending order
    DENSE // Use DENSE for no gaps in ranking, or SKIP for standard ranking
)

Breakdown of the Measure #

• PriorityGroup: Assigns 2 to items hitting/exceeding 80%, 1 to those below. Multiplying by 1000 ensures the group weight dominates the rate value (so even the lowest target-meeting rate will be higher than the highest non-target rate).
• ALLSELECTED: Ensures ranking is calculated within the current time dimension filter context of your pivot table (it respects any date slicers/filters you've applied).
• RANKX Parameters: The DESC argument sorts the combined PriorityGroup*1000 + CurrentRate value from highest to lowest, and DENSE gives you consecutive ranks without gaps (adjust to SKIP if you want standard ranking with gaps for ties).

How to Use in Pivot Table #

1. Add your time dimension (e.g., Date, Month) to the pivot table's Rows/Columns area.
2. Add your item category (A/B/C/D/etc.) to Rows.
3. Drag the Target-Based Rank measure to Values, then set the pivot table to sort by this rank measure in ascending order (since higher combined values get lower rank numbers).

This should perfectly align with your desired sorting order while working within OLAP pivot table restrictions.

内容的提问来源于stack exchange，提问作者J.1