RankX help

[Anonymous]

I've tried a number of solutions reading through these forums and have learned quite a bit. But I'm still struggling with this particular Ranking. Here is a sample of what my data looks like.

sup_nam	Count of Reason	Month Name 3
Manager 1	16	January
Manager 1	12	February
Manager 2	11	January
Manager 2	21	February
Manager 3	20	January
Manager 3	15	February

When I apply the rank using a filter for one month I get the rank I'm looking for.

Filtered Jan	Count of Reason	Rank
Manager 2	12	January	1
Manager 1	16	January	2
Manager 3	20	January	3

However when I have multiple months selected I get where each Manager is ranked for each month instead of being ranked by the total reasons.

Filtered Jan & Feb	Count fo Reason	Rank
Manager 2	11	January	1
Manager 1	12	February	2
Manager 3	15	February	3
Manager 1	16	January	4
Manager 3	20	January	5
Manager 2	21	February	6

Here is my measure; 

RankingMeasure =

IF (

    ISFILTERED ( 'Manager Totals By Month'[Month Name 3] ),

    RANKX (

            ALLSELECTED ( 'Manager Totals By Month' ),

            CALCULATE ( SUM ( 'Manager Totals By Month'[CountofReason] ) ),

            ,

            asc

     ),

    RANKX (

            ALL ( 'Manager Totals By Month' ),

            CALCULATE ( SUM ( 'Manager Totals By Month'[CountofReason] ) ),

            ,

            asc

    )

)

Thanks for all the previous help and the help on this.

[Anonymous]

I forgot to add what I'm looking for. I'm looking for the ranking on all the totals for each manager for the selected months. Such as Jan & Feb selected;

Manager 1	28	N/A	1
Manager 2	32	N/A	2
Manager 3	35	N/A	3

[Anonymous · Accepted solution]

How about this:

RANKX = 
IF ( 
    HASONEVALUE( 'RankX Help'[sup_nam]),
    RANKX( 
        ALL('RankX Help'[sup_nam]), 
        [Total Count of Reason],,ASC,Dense
    )
)

Looks like it produces what you are after:

[Anonymous]

In your example is [Total count of Reason] a new measure? I'm having trouble replicating your formula, when I try to type in that particular field does not pop up.

[Anonymous]

Nevermind on my last post. Got it created new measure summing Count of reason. Was having troubl of rank showing all 2's but swapped count of reason out for for my new measure and it perfectly. 

Thank you so much Nick!!

[Anonymous]

Yes, sorry about that, it was a new measure. If you do not use a measure there you need to be sure to use CALCULATE in the expression in order to invoke context transition. If not you will get the same values! I like to use measures since 1) I may be able to use them else where, 2) If i have to make a change, just have to make it once and 3) using a measure has an implied calculate wrapped around it, so don't need to work.  

Glad it helped!

[Anonymous]

The tip regarding measures is great. I like all the points you listed. I will definitley have to start using them more.

Thanks again!