Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with comparing rankings

Hi
I'm ranking some people on my report, month by month but now I need to see if the rank of the selected month is greater o lower (and how many positions) versus the previous period
example

my formula for the ranking is this:

Rank Usuario = RANKX(
FILTER('rank_men_promo_aut',
AND('rank_men_promo_aut'[Mes]=EARLIER('rank_men_promo_aut'[Mes]),'rank_men_promo_aut'[Año]=EARLIER('rank_men_promo_aut'[Año]))),
'rank_men_promo_aut'[EF Mens],,DESC,Skip)
Then my DBB look like this:
I've tried but my measure just gives me the same rank for the actual and the previous month
Can anybody help me please

 

Need Help
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi IIN_role,

     

    Do you want to compare this month's rank with last month's rank? IF so, please refer to the following steps:

     

    1.Create a calculated column as a parameter month1 to get the month of the previous month:

     

     

    month1 = 'rank_men_promo_aut'[Año]*12 + 'rank_men_promo_aut'[Mes]

     

     

    2.Create a ranking column to rank by month, You already have this ranking on your chart:

     

     

    Rank =

RANKX (

    FILTER (

        'rank_men_promo_aut',

        'rank_men_promo_aut'[Año] = EARLIER ( 'rank_men_promo_aut'[Año] )

            && 'rank_men_promo_aut'[Mes] = EARLIER ( 'rank_men_promo_aut'[Mes] )

    ),

    'rank_men_promo_aut'[EF Mens],

    ,

    ASC,

    DENSE

)

     

     

    3.Create a measure to get last month's rankings:

     

     

    ranklaset month =

VAR mindate =

    CALCULATE ( MIN ( 'rank_men_promo_aut'[month1] ), ALL ( 'rank_men_promo_aut' ) )

VAR pre =

    CALCULATE (

        MAX ( 'rank_men_promo_aut'[month1] ),

        FILTER (

            ALLSELECTED ( 'rank_men_promo_aut' ),

            'rank_men_promo_aut'[month1] < MAX ( 'rank_men_promo_aut'[month1] )

        )

    )

RETURN

    IF (

        MAX ( 'rank_men_promo_aut'[month1] ) = mindate,

        BLANK (),

        CALCULATE (

            MAX ( 'rank_men_promo_aut'[Rank] ),

            FILTER (

                ALLSELECTED ( 'rank_men_promo_aut' ),

                'rank_men_promo_aut'[month1] = pre

            ),

            VALUES ( 'rank_men_promo_aut'[Usuario] )

        )

    )

     

     

    4.Create a column to subtract the previous month's ranking from this month's ranking, and if it is the smallest month, it will be 0：

     

     

    compare =

IF (

    'rank_men_promo_aut'[Rank] - [ranklaset month] = 'rank_men_promo_aut'[Rank],

    0,

    'rank_men_promo_aut'[Rank] - [ranklaset month_]

)

     

     

    The following is the test sample I created for your reference. You can download the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXKCdGtszHJMgcCZgHtHKw0BjM59bPdCiW-Me2zJWIzFPQ?e=KG59K8

     

     

     

     

    Best Regards,

    Dedmon Dai

1 Reply

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi IIN_role,

     

    Do you want to compare this month's rank with last month's rank? IF so, please refer to the following steps:

     

    1.Create a calculated column as a parameter month1 to get the month of the previous month:

     

     

    month1 = 'rank_men_promo_aut'[Año]*12 + 'rank_men_promo_aut'[Mes]

     

     

    2.Create a ranking column to rank by month, You already have this ranking on your chart:

     

     

    Rank =

RANKX (

    FILTER (

        'rank_men_promo_aut',

        'rank_men_promo_aut'[Año] = EARLIER ( 'rank_men_promo_aut'[Año] )

            && 'rank_men_promo_aut'[Mes] = EARLIER ( 'rank_men_promo_aut'[Mes] )

    ),

    'rank_men_promo_aut'[EF Mens],

    ,

    ASC,

    DENSE

)

     

     

    3.Create a measure to get last month's rankings:

     

     

    ranklaset month =

VAR mindate =

    CALCULATE ( MIN ( 'rank_men_promo_aut'[month1] ), ALL ( 'rank_men_promo_aut' ) )

VAR pre =

    CALCULATE (

        MAX ( 'rank_men_promo_aut'[month1] ),

        FILTER (

            ALLSELECTED ( 'rank_men_promo_aut' ),

            'rank_men_promo_aut'[month1] < MAX ( 'rank_men_promo_aut'[month1] )

        )

    )

RETURN

    IF (

        MAX ( 'rank_men_promo_aut'[month1] ) = mindate,

        BLANK (),

        CALCULATE (

            MAX ( 'rank_men_promo_aut'[Rank] ),

            FILTER (

                ALLSELECTED ( 'rank_men_promo_aut' ),

                'rank_men_promo_aut'[month1] = pre

            ),

            VALUES ( 'rank_men_promo_aut'[Usuario] )

        )

    )

     

     

    4.Create a column to subtract the previous month's ranking from this month's ranking, and if it is the smallest month, it will be 0：

     

     

    compare =

IF (

    'rank_men_promo_aut'[Rank] - [ranklaset month] = 'rank_men_promo_aut'[Rank],

    0,

    'rank_men_promo_aut'[Rank] - [ranklaset month_]

)

     

     

    The following is the test sample I created for your reference. You can download the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXKCdGtszHJMgcCZgHtHKw0BjM59bPdCiW-Me2zJWIzFPQ?e=KG59K8

     

     

     

     

    Best Regards,

    Dedmon Dai