Forum Discussion
Anonymous
5 years agoNot applicable
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...
- Anonymous5 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] descThe 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.
Anonymous
5 years agoNot applicable
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] descThe 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.