Ranking members within group

DAX DAX Calculations
13

@Mark,

Okay, I think I finally cracked this one in a way that’s fully dynamic. The tricky part here is maintaining proper context. The first TOPN 1 measures to identify the top salesperson per region by sales and profit are pretty standard TOPN, operating in context:

Top 1 Salespeople Sales per Region = 

VAR RankingContext = VALUES( Salespeople[Salesperson Name] ) 

RETURN
CALCULATE( 
    [Total Sales],
    TOPN(
        1,
        ALL( Salespeople[Salesperson Name] ),
        [Total Sales] ),
    RankingContext
)

What gets tricky is someone ranked #1 in their region in sales may not be in profit and vice versa. Thus, for the ranking of the TOPN results, we need to take location and salesperson together in context explicitly within the measure via virtual table, since now a location can have multiple salespeople in the context (one qualifying as a #1 in sales, and a second qualifying as a #1 in profit)

RankXSales = 

VAR vTable =
FILTER(
    CROSSJOIN(
        ALL( Locations[Name] ),
        ALL( Salespeople[Salesperson Name] )
    ),
    [Top 1 Salespeople Sales per Region] <> BLANK()
)

VAR FilteredRanking =
   RANKX(
       vTable,
        [Top 1 Salespeople Sales per Region],,
        DESC,
        Dense
    )

RETURN
FilteredRanking

Here it is all put together:

image
image704×554 38.4 KB

I hope this is helpful. Full solution file attached below,
eDNA Forum - Combing Ranks to give FINAL RANK 2 Solution.pbix (441.2 KB)

1 Like