I have a query where after merging a few tables together, I'm trying to rank data based on three calculated metrics that are individually ranked. Those metrics are:

我有一个查询，在将几个表合并在一起后，我尝试根据三个计算出的指标对数据进行排名，这三个指标是单独排名的。这些指标包括：

• IP_PER_DAY


• CR (Close Rate)


• AVERAGE TICKET

What I'm doing is adding up the ranked values and then ranking that number but if there is a tie between the summed ranked values, PQ just assigns the same ranking (RankKind=Competition). What I'm wanting to do is sum up the rankings for the three metrics above and IF there is a tie between the three summed rankings, I'd like to use the highest SALES_PCT_TO_PLAN as the tiebreaker.

我所做的是将排名值相加，然后对该数字进行排名，但如果总和排名值之间存在平局，PQ只会分配相同的排名(RankKind=竞争)。我想要做的是对上述三个指标的排名进行汇总，如果三个指标的排名总和相等，我希望使用最高的Sales_PCT_TO_PLAN作为决胜局。

Wisdom is greatly appreciated.

智慧是非常值得欣赏的。

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",{"IPASS / Day",Order.Descending}),
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 [#"IPASS / Day Rank"]+[Close Rate Rank]+[Average Ticket Rank]),
SALES_PCT_TO_PLAN_RANK = Table.AddRankColumn(TOTAL_POINTS,"Sales % to Plan Rank",{"SALES_PCT_TO_PLAN",Order.Descending}),
POWER_RANK = Table.AddRankColumn(SALES_PCT_TO_PLAN_RANK,"ACT Power Rank",{"TOTAL_POINTS",Order.Ascending},[RankKind=RankKind.Competition])

in
POWER_RANK

在权力排名中

Add the column to your list of columns in the order you wish it considered.

按您希望的顺序将该列添加到列列表中。

POWER_RANK = Table.AddRankColumn(SALES_PCT_TO_PLAN_RANK,"ACT Power Rank",{"TOTAL_POINTS","Sales % to Plan Rank"},[RankKind=RankKind.Competition])

Note that the above relies on the default Order.Ascending sort order. You can expand the argument to include the desired sort order for each column with the same syntax used in Table.Sort

请注意，上面的排序依赖于默认的Order.Ascending排序顺序。您可以使用与Table.Sort中使用的语法相同的语法扩展参数，以包括每列所需的排序顺序

    #"Add Rank Column" = Table.AddRankColumn(#"Previous Step","Rank", 
        {
            {"Column1",Order.Ascending},
            {"Column2",Order.Ascending}
        },
            [RankKind=RankKind.Competition])