Top Performers Analysis + Generate() and TopN() Tutorial

Ok, I have to admit, this is not a level 100 kind of stuff, things get a little complicated, but if you manage to sit through the entire thing, I think it will be worth your while. The problem is the following – we have a sales table tracking Sales Amount by Date, Product, and a Salesperson. Every week there is a single person who sells more than everyone else. In a single month you might have 1 or more (up to number of weeks in this month) Salespersons who qualify for this designation. To make these things more complicated, we might want to slice and dice by product. So we want to know for each week who the top person is and also how many time did he/she qualify to be the top performer.

I am employing two of my favorite functions, Generate() and TopN() to help me solve this problem. Also, I am starting to use DAX Studio to break my calculations in smaller chunks hoping that it would make it easier for you guys to follow and understand. Let me know if you find this technique useful.

You can download the model with test data here.

isTopSalesPerson = 

var topSellers = 
    CALCULATETABLE(
        GENERATE(
            VALUES('Date'[WeekNo]), 
            TOPN(
                1, 
                ADDCOLUMNS(
                    VALUES('Sales Person'[SalesPersonID]),
                    "WeeklySales",
                    [Sales]
                ),
                [WeeklySales],
                DESC
            )
        ),
        ALL('Sales Person')
    )

var isTop = CONTAINS(topSellers, [SalesPersonID], SELECTEDVALUE(Sales[SalesPersonID]))
 
return IF(isTop, 1, 0)

TopPerformerCount = 

var topSellers = 
    CALCULATETABLE(
        GENERATE(
            VALUES('Date'[WeekNo]), 
            TOPN(
                1, 
                ADDCOLUMNS(
                    VALUES('Sales Person'[SalesPersonID]),
                    "WeeklySales",
                    [Sales]
                ),
                [WeeklySales],
                DESC
            )
        ),
        ALL('Sales Person')
    )

return 
COUNTROWS(FILTER(topSellers, 
            [WeeklySales]>0 && 
            [SalesPersonID]=SELECTEDVALUE('Sales Person'[SalesPersonID])))