Rankx Skipping a number in Dynamic Rank Measure with What-If Analysis

[Anonymous]

The goal is to dynamically rank employees based on average performance, where date, program, employee name and %contribution (as a what-if analysis scenario measure) is used.

Rank Code: 

'Dim.Employee'[Employeefullname] is used to filter multiple visuals at the same time, hence the crossjoin.

Performance Rank = 
    IF(
        HASONEVALUE('dim Employee'[EmployeeFullName]),
        RANKX(
            CROSSJOIN(
                ALL('dim Employee'[EmployeeFullName]), 
                ALL( 'fact Work Order Details'[EmployeeFullName] )
                ), 
            [Average Performance],,DESC, Dense), 
        "-"
    )

Average Performance Measure Code (where level closed is the %contribution set by the what if analysis slicer):

Average Performance = 
    CALCULATE(
        AVERAGEX('fact Work Order Details','fact Work Order Details'[Performance]), 
        FILTER('fact Work Order Details', 'fact Work Order Details'[PercentWork] >= 'Level Closed'[Level Value])
        )

However, on some values, when a single employee is selected, it skips a number. The current Rank below should be 11, but it is giving 12. I also have an audit table. Where it ranks properly in the table but gives 12 in the total. Not sure what is going on there. It does not do this for all the other employee selections.

Faulty Result: 

[amitchandak · Accepted solution]

@Anonymous , first of all the dimesion should join to the fact names and only dimension should be use .

You should try like

RANKX(
            CROSSJOIN(
                ALL('dim Employee'[EmployeeFullName]), 
                ), 
            [Average Performance],,DESC, Dense)

If the tables are joined then

RANKX(
            summarize(all('fact Work Order Details')
                ('dim Employee'[EmployeeFullName]), 
                ( 'fact Work Order Details'[EmployeeFullName] )
                ), 
            [Average Performance],,DESC, Dense)

[Anonymous]

Thanks Amit! 

I updated the Rankx statement to use only the dim column but that didn't fix the issue. 

I decided to iterate through the different inputs and finally found what the issue was. I had put the table filter outside of the AverageX function. Not sure what was wrong but the rankx function didn't like that. 

I update the scripts to this, and it works perfectly now. Your recommendation did clean up the query though. Thanks!

Calculation Measure:

Average Performance = 
    CALCULATE(
        AVERAGEX(
            FILTER('fact Work Order Details', 'fact Work Order Details'[PercentWork] >= 'Level Closed'[Level Value]),
            'fact Work Order Details'[Performance])
        )

Rankx Measure:

Performance Rank = 
    IF(
        HASONEVALUE('dim Employee'[EmployeeFullName]),
        RANKX(
                ALL('dim Employee'[EmployeeFullName]), 
            [Average Performance],,DESC, Skip), 
        "-"
    )