Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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?

@Power BI
DAX Formulas
DAX RANKX
ranking
  • johnt75's avatar
    johnt75
    3 years ago

    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.

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        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.