Problem when creating a ranking using several tables in Power BI DAX

[Anonymous]

Hi team.

I'm trying to do this, for each couple year-month I need to create the ranking of my revenue. The rankin must restart everytime the year changes. This is an image of my model

And this is the formula that I've created:

Ranking Revenue =

VAR RevenuePerMonth =

    SUMX('sales', 'sales'[quantity] * RELATED('products'[price]))

RETURN

    RANKX(

        FILTER(

            ALL('datedim'),

            'datedim'[MonthNo] = MAX('datedim'[MonthNo]) ||

            'datedim'[Year] = MAX('datedim'[Year])

        ),

        RevenuePerMonth,

        ,

        ASC,

        Dense

    )

However, I'm getting always the same value when I create the table chart using the year and monthNo from the datedim table:

What is missing or what is wrong?

[johnt75 · Accepted solution]

Variables in DAX aren't really variables, they're constants, so the RevenuePerMonth variable is only calculated once, it is not recalculated for each year / month combination. 

Take your code from the variable declaration and use it to create a new measure, and then reference that measure in your ranking code.

[Anonymous]

Thanks @johnt75  for your help. I have made the modifications and right now the metric is working. However, I would like to understand something. If I write this expression the ranking is ok:

Ranking Revenue Test =

    RANKX(

        ALL(DateDim[YYYYMM],'DateDim'[MonthNo]),

        [Revenue Measure],,

        DESC,

        Dense

    )

But, when I write this one the metric always returns the same value 1:

Ranking Revenue Test =

    RANKX(

        ALL(DateDim[YYYYMM],'DateDim'[MonthNo]),

        SUMX(sales, sales[quantity] * RELATED(products[price] )),,

        DESC,

        Dense

    )

What is happening in the second definition?

[johnt75]

In the first version context transition is happening because you are calling a measure. In the second it is not happening and so the same value is being calculated for every row. If you wrapped the SUMX inside CALCULATE then it would work OK.