topic Re: Rank based on how many goals hit, then % to Goal in DAX Commands and Tips

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

KW123 — Thu, 08 May 2025 04:50:45 GMT

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!!

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

bhanu_gautam — Thu, 08 May 2025 06:18:19 GMT

KW123 , Try using

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

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

KW123 — Thu, 08 May 2025 17:25:47 GMT

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.

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

techies — Fri, 09 May 2025 05:02:01 GMT

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

)

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

KW123 — Fri, 09 May 2025 18:28:29 GMT

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.

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

techies — Sat, 10 May 2025 05:11:06 GMT

Hi KW123 please try this

Rank =

RANKX(

ALLSELECTED('Employees'),

[Goals_Hit] * 1000 + [Total_Percentage],

DESC,

Dense

)

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

KW123 — Sun, 11 May 2025 22:57:36 GMT

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