Rank based on how many goals hit, then % to Goal

[KW123]

Hi there, 

I am trying to rank employees based on if they have hit their 3 different monthly goals and then rank them by the % to goal.  

Employee	Sales Item 1	Goal 1	% To goal 1	Sales Item 2	Goal 2	% To Goal 2	Sales Item 3	Goal 3	% To Goal 3	How many goals hit (/3)	% to Total Goal (30)	Rank
A	5	5	100%	10	10	100%	15	15	100%	3	300%	1
B	5	5	100%	9	10	90%	4	15	27%	2	217%	3
C	1	5	20%	2	10	20%	20	15	133%	1	173%	5
D	7	5	140%	1	10	10%	10	15	67%	1	217%	4
E	2	5	40%	15	10	150%	25	15	167%	2	357%	2

In the example above, employee A has a lower total % to goal than employee E, but employee A hit all three goals which puts them higher ranked.  

My three Sales Items columns are all Measures that I calculated.  I tried using the following DAX where 'Table'[GPA] = 'Table'[How Many goals hit /3] and [income] = [% to total Goal]

RankedByGPA(Income) = 
RANKX(ALL('Table'), 
    RANKX (ALL('Table'), 'Table'[GPA])
        + DIVIDE( 
            RANKX(ALL('Table'), 'Table'[Income]), 
            (COUNTROWS(ALL('Table')) + 1)
        )
, , ASC)

But I believe my measures are creating the wrong calculations.  

Any guidance would be much appreciated!! 

[techies]

Hi @KW123 please try using these 3 measures

Goals_Hit measure =

VAR Goal1Hit = IF(SUM('Employees'[Sales Item 1]) >= SUM('Employees'[Goal 1]), 1, 0)

VAR Goal2Hit = IF(SUM('Employees'[Sales Item 2]) >= SUM('Employees'[Goal 2]), 1, 0)

VAR Goal3Hit = IF(SUM('Employees'[Sales Item 3]) >= SUM('Employees'[Goal 3]), 1, 0)

RETURN Goal1Hit + Goal2Hit + Goal3Hit

Total_Percentage measure=

SUM('Employees'[% goal1]) + SUM('Employees'[% goal2]) + SUM('Employees'[% goal3])

Rank measure =

RANKX(

    ALL('Employees'),

    [Goals_Hit] * 1000 + [Total_Percentage],

    ,

    DESC,

    Dense

)

[KW123]

Thank you for the reply!! 

That is so close to working.  I created the exact measures you listed however it is ranking the empoyees as a whole rather for just the selected time period.  See screen shot below.  

Additionally, I tried to use the PBI filter to only show the top 5 (which is what the stakeholder has requested) and it is not showing the top 5. 

[techies · Accepted solution]

Hi @KW123 please try this

Rank =

RANKX(

    ALLSELECTED('Employees'),

    [Goals_Hit] * 1000 + [Total_Percentage],

    ,

    DESC,

    Dense

)

[KW123]

Thank you so much!!!! That worked!!! I really appreciate your help with this. 

[bhanu_gautam]

@KW123 , Try using

DAX
RankedByGoalsAndIncome =
RANKX(
ALL('Table'),
'Table'[How many goals hit (/3)] +
DIVIDE('Table'[% to Total Goal (30)], 1000),
,
DESC
)

[KW123]

Thank you so much for that!! 

I tried it and It returned values of all 1.  I am not sure if it is because of my measures?

The measure for each of the three sales items are  
[SalesItem1] =

CALCULATE (

    DISTINCTCOUNT ( 'Sales'[SalesID1]),

    FILTER (

       ALLSELECTED ( 'Dates'),

        [Date]

    IN FILTERS ( 'Dates'[Date] )

    )

)

[MeetsGoalSalesItem1] = 

IF (SUM('Sales'[SalesItem1]) >= 'Goals'[GoalSalesItem1], 1, 0)

[GoalSalesItem1] = 

CALCULATE (

    SUM ( 'Goals'[Goal1]),

    FILTER (

        ALLSELECTED ( 'Dates'),

        [Date]

    IN FILTERS ( 'Dates'[Date] )

    )

)

The Goals Met Column in the above SS is just [MeetsGoalSalesItem1] + [MeetsGoalSalesItem2] + [MeetsGoalSalesItem3]

For the % Goal Total, it is the same calculations as above, just %

Hopefully that provides some context behind the measures.