Power Query中带平局决胜规则的排名实现方法求助

当前通过三个指标（IP_PER_DAY、CR成交率、AVERAGE_TICKET客单价）的排名值求和后做竞争排名，但平局时无法用SALES_PCT_TO_PLAN（销售完成率）区分名次。解决方法很简单：直接在最终排名步骤里同时指定两个排序依据，不需要单独给SALES_PCT_TO_PLAN做排名。

修改核心 #

删掉原来单独生成Sales % to Plan Rank的步骤，在最终的ACT Power Rank步骤中，将排序条件改为先按TOTAL_POINTS升序（求和值越小排名越靠前），再按SALES_PCT_TO_PLAN降序（完成率越高排名越靠前）。Power Query会自动在TOTAL_POINTS平局时，用第二个条件区分名次。

修改后的完整代码 #

let
    Source = #"365",
    #"Grouped Rows" = Table.Group(Source, {"REGION"}, {{"I_SALES", each List.Sum([I_SALES]), type nullable number}, {"NO_OF_I", each List.Sum([NO_OF_I]), type number}, {"I_PLAN", each List.Sum([I_PLAN]), type nullable number}, {"IP_ISSUED", each List.Sum([IP_ISSUED]), type nullable number}, {"IP_REJECTED", each List.Sum([IP_REJECTED]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TIMEFRAME", each 365),
    SALES_PCT_TO_PLAN = Table.AddColumn(#"Added Custom", "SALES_PCT_TO_PLAN", each [I_SALES]/[I_PLAN]),
    IP_PER_DAY = Table.AddColumn(SALES_PCT_TO_PLAN, "IP / Day", each ([IP_ISSUED]-[IP_REJECTED])/[TIMEFRAME]),
    IP_DAY_RANK = Table.AddRankColumn(IP_PER_DAY,"IP / Day Rank",{"IP / Day",Order.Descending}), // 修正原代码字段名错误："IPASS / Day"改为实际生成的"IP / Day"
    CR = Table.AddColumn(IP_DAY_RANK, "CR", each [NO_OF_I]/([IP_ISSUED]-[IP_REJECTED])),
    CR_RANK = Table.AddRankColumn(CR,"Close Rate Rank",{"CR",Order.Descending}),
    AVERAGE_TICKET = Table.AddColumn(CR_RANK, "AVERAGE_TICKET", each [I_SALES]/[NO_OF_I]),
    AVERAGE_TICKET_RANK = Table.AddRankColumn(AVERAGE_TICKET,"Average Ticket Rank",{"AVERAGE_TICKET",Order.Descending}),
    TOTAL_POINTS = Table.AddColumn(AVERAGE_TICKET_RANK, "TOTAL_POINTS", each [#"IP / Day Rank"]+[Close Rate Rank]+[Average Ticket Rank]), // 同步修正字段引用
    POWER_RANK = Table.AddRankColumn(TOTAL_POINTS,"ACT Power Rank",
        {
            {"TOTAL_POINTS", Order.Ascending},
            {"SALES_PCT_TO_PLAN", Order.Descending}
        },
        [RankKind=RankKind.Competition]
    )
in
    POWER_RANK

额外说明 #

原代码存在一处字段名错误：IP_DAY_RANK步骤中排序字段写为"IPASS / Day"，但实际生成的字段是"IP / Day"，已同步修正，避免运行报错。

内容的提问来源于stack exchange，提问作者Sam R.