Top 1 in one Table rest in Another Table - Tie Breaker - Missing Ranks

[Maha2022]

Hi All, 

I'm really getting my head hurt by thinking of the solution for this problem below: 

I have a Data Table below which Ranked the Performers based on 4 Criteria Listed. Each criteria will be assigned some scores based on some conditions. These 4 scores will be added together and get the Total Score. Based on this Total Score, Rank will be provided. If any criteria is not met, then Rank wont be provided for them. This is the Data Table I have created. Now the problem is 

1. Some Ranks are missing in my data what is the reason for that.

2. I have to create a tie breaker for this Rank based on Criteria 3 & Criteria 4. How to do that? 

The major issue which I'm facing is I also have to create 2 other tables from this table. One table should bring the top 1 performer from each region and the other table should bring the rest of the 10 performers excluding the top 1 in each region (attached screenshot for reference) 

Date Table

Yellow Highlighted in First table and green highlightd in the 2nd table Yellow highlighted in Table 1 and Greenhighlighted in Table 2

[Maha2022]

I used below measure to get Table 1

RegionToppers =

VAR Top1Region =

    CALCULATETABLE (

        GENERATE (

            VALUES ( 'Employee'[Region]),

            TOPN (

                1,

                CALCULATETABLE ( VALUES ( 'Employee'[Employee Name] )

                ),

                [Total Score],ASC

            )

        ),

        ALLSELECTED()

    )

RETURN

    CALCULATE (

        1 * ( NOT ISEMPTY ( 'Employee' ) ),

        KEEPFILTERS ( Top1Region )

    )

This measure mapped 1 for the region toppers and 0 for the rest. So I tried to filter the RegionToppers Measure as 1 to display only region toppers but im not seeing any data for that filter. Table becomes empty. What could be the reason for it.

[Maha2022]

Any idea please?  how to get static top 1 Employee for each region by including all the filters applied in the page. 

[amitchandak]

@Maha2022 , for Rank tie breaker, check if these can help

Rank Tie breaker
https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-...
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

[Maha2022]

For Tie Breaker, I've tried the below

Rank =
Var summry=SUMMARIZE(All('Table'),
Table[Employee Name],
"TotalScore",[Total Score],
"Rate",[Rate%],
"NoofCustomers",[CustomerCount])
var tmp=ADDCOLUMNS(summry,"RNK",
RANKX(summry,
RANKX(summry,
RANKX (summry, [TotalScore])
+ DIVIDE(
RANKX(summry, [Rate]),
(COUNTROWS(summry) + 1)
)
, , DESC) +
+ DIVIDE(
RANKX(summry,[CustomerCount], , DESC),
(COUNTROWS(summry) + 1)
)
, , ASC)
)
var rnk = MAXX(FILTER(tmp,Table[Employee Name]=SELECTEDVALUE(Table[Employee Name])),[RNK])
return rnk

Now the problem is it ignoring the Page Filters and giving ranks based on it. I want to include Page Filters as well in this. 

The Ouput which Im getting is 

Employee Name  Rank

Employee1      1

Employee3      3

Employee5      5

Employee8      10

I checked why so many ranks are missing. There i could see the Table data works based on page filters. Ex. I have included some other filters in page as employees who only have more than 20% should be displayed. So as per my condition the Employee Names are displaying in the Table Data. But Rank is applied to filtered data as well and it is not showing in the output. How do i add rank only for the available data and not for the filtered out data. 

Expected Output 

Employee Name  Rank

Employee1      1

Employee3      2

Employee5      3

Employee8      4

[Maha2022]

I'll check that thank you