Forum Discussion

nicolas23498324's avatar
nicolas23498324
Frequent Visitor
5 years ago
Solved

Creating Ranking by Week Measure on top of Virtual Tables

Hello,   My dashboard tracks volume collections daily. I've been able to use RANKX to create a daily ranking measure without an issue. What I would like to do now is give the current selected week ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi nicolas23498324 ,

     

    You could create a weeknum column.

    Please check the formulas below.

     

    weeknum = WEEKNUM('Table'[date],1)

Measure = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[weeknum]))

Measure 2 = RANKX(ALLSELECTED('Table'[weeknum]),[Measure],,DESC,Dense)

     

     

    Best Regards,

    Jay

nicolas23498324's avatar
nicolas23498324
Frequent Visitor
5 years ago

Hi Amitchandak,

 

Thanks for the link. Using that approach I have successfully created a table with a ranking per week. However, I cannot figure out how to use that logic to display a measure that shows the ranking of the current week.

I understand that my attempt below fails because it removes the other weeks which it is ranking against. I guess what I want is a lookup function to return the ranking (e.g. 11) based on the elected date matched against WEEK_END. Can you suggest how I would do that?

 

*Simple Rank = 
CALCULATE(
    RANKX(
        ALL(ORACLE_DATE_DIMENSION[WEEK_END]),
        [Claim_Volume_STD],
    ),
    FILTER(
        ORACLE_DATE_DIMENSION,
        ORACLE_DATE_DIMENSION[WEEK_END]=[pm_Selected_Period_Week_Ending]
    )
)