Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Challenge ?

Hi All,   I have the following sales table where I need to find annual ranking of salesperson.   There is a small complication....   The ranking depends not only on number of annual car sales b...
  • Anonymous's avatar
    Anonymous
    5 years ago
    DEFINE
    MEASURE 'Ranking Exercise'[Total Sales] =
        SUM( 'Ranking Exercise'[Annual Car Sales] )
        
    MEASURE 'Ranking Exercise'[Tenure Days] =
        // You need to create a column 'Ranking Exercise'[Days With Company]
        // that will equal Today() - RankingExercise[Date of Joining].
        // or you can replace the SUM below with:
        //    SUMX(
        //        'Ranking Exercise',
        //        TODAY() - 'Ranking Exercise'[Date of Joining]
        //    )
        SUM( 'Ranking Exercise'[Days With Company] )
    
    MEASURE 'Ranking Exercise'[Employee Rank] =
    IF( ISINSCOPE( 'Ranking Exercise'[FIRST_NAME] ),
        var vEmps =
            ALLSELECTED(
                'Ranking Exercise'[FIRST_NAME] 
            )
        var vEmpsWithRanks =
            ADDCOLUMNS(
                vEmps,
                "@EmpRank",
                    CALCULATE(
                        RANKX(
                            vEmps,
                            [Total Sales],,
                            DESC
                        )                
                    )
            )
        var vCurrentEmp = SELECTEDVALUE( 'Ranking Exercise'[FIRST_NAME] )
        var vCurrentEmpRank =
            MAXX(
                FILTER(
                    vEmpsWithRanks,
                    'Ranking Exercise'[FIRST_NAME] = vCurrentEmp
                ),
                [@EmpRank]
            )
        var vEmpsWithSameRank =
            FILTER(
                vEmpsWithRanks,
                [@EmpRank] = vCurrentEmpRank
            )
        var vThereAreOthersWithSameRank =
            COUNTROWS( vEmpsWithSameRank ) > 1
        var vResult =
            if( not vThereAreOthersWithSameRank,
                vCurrentEmpRank,
                
                // Rank these others with the current one
                // based on days with company where a
                // lower rank means shorter period with
                // the company.
                
                vCurrentEmpRank - 1 +
                RANKX(
                    vEmpsWithSameRank,
                    [Tenure Days],,
                    ASC
                )
                
            )
        RETURN
            vResult
    )
            
    EVALUATE
        SUMMARIZECOLUMNS(
            'Ranking Exercise'[FIRST_NAME],
            "Total Sales", [Total Sales],
            "Tenure Days", [Tenure Days],
            "Employee Rank", [Employee Rank]
        )
    ORDER BY
        [Employee Rank],
        [Total Sales] desc,
        [Tenure Days] desc

    The code above is a DAX query that demonstrates how to construct a ranking measure that satisfies your requirements. Out of this, it's easy to just write the measure in PBI. Actually, it's already been written. It's 'Ranking Exercise'[Employee Rank]. You just have to adjust the names of some objects.