0

For each couple year-month I need to create the ranking of my average revenue. So far this is what I have been able to reach:

enter image description here

However, what I need to do is to re-start the ranking when the year changes. But righ now is a consecutive ranking, it does not consider the year, just the value.

I tried another metric definition using some indications but the result was always 1:

Monthly Rank by Year = 
VAR current_year = SELECTEDVALUE(DateDim[Year])
VAR tmp = FILTER(ALL(DateDim), DateDim[Year] = current_year)
RETURN 
RANKX(tmp, [Monthly Average Revenue],,DESC, Dense)

How can I fix this calculation.

d2907
  • 798
  • 3
  • 15
  • 45

1 Answers1

0

First write a measure

MVR = SUM(Data[Monthly Average Revenue])

Then write Rank measure

Rank = RANKX(ALLEXCEPT(Data, Data[Year]), [MVR], , DESC, Dense)

enter image description here

Ashok Anumula
  • 1,338
  • 1
  • 4
  • 9