Creating Ranking by Week Measure on top of Virtual Tables

[nicolas23498324]

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 a ranking in comparison with all historic weeks.

I've been able to create this ranking in a table as follows:

*temp = 
VAR table1 = SUMMARIZECOLUMNS(
            ORACLE_DATE_DIMENSION[week_end],
            FILTER(
                ORACLE_DATE_DIMENSION, 
                ORACLE_DATE_DIMENSION[WEEK_END]<=[pm_Selected_Period_Week_Ending]
            ),
            "Claim Volume",
            SUM(CLAIM_VOLUMES_DAILY[CLAIM_VOLUME_ADJUST])
    )

VAR table_new = 

ADDCOLUMNS(
    SUMMARIZE(
        table1,
        ORACLE_DATE_DIMENSION[WEEK_END],
        [Claim Volume]
    ),
    "Rank1", RANKX(table1,[Claim Volume],,DESC)
)

return
table_new

The top 5 from that table looks like this:

WEEK_END	Claim Volume	Rank1
13/12/2020 0:00	41971596	3
6/12/2020 0:00	40483848	4
4/10/2020 0:00	37770213	5
12/01/2020 0:00	42980021	1
22/12/2019 0:00	42534565	2

Now I'd like to create a measure that references these virtual tables but returns the "Rank1" value based on which "WEEK_END" has been selected. I have tried to use the above variables and the following expression, but DAX does not seem to recognize 'table_new' as a proper variable:

*Week collection rank = 
VAR table1 = SUMMARIZECOLUMNS(
            ORACLE_DATE_DIMENSION[week_end],
            FILTER(
                ORACLE_DATE_DIMENSION, 
                ORACLE_DATE_DIMENSION[WEEK_END]<=[pm_Selected_Period_Week_Ending]
            ),
            "Claim Volume",
            SUM(CLAIM_VOLUMES_DAILY[CLAIM_VOLUME_ADJUST])
    )

VAR table_new = 
CALCULATE(
ADDCOLUMNS(
    SUMMARIZE(
        table1,
        ORACLE_DATE_DIMENSION[WEEK_END],
        [Claim Volume]
    ),
    "Rank1", RANKX(table1,[Claim Volume],,DESC)
))
return
maxx(table_new,[Rank1])

That code returns the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." The "table_new" gets the red squiggly line, as does "Rank1".

Am I correct that only one virtual table can come through as a variable when used in a measure?

If so, how can I complete this calculation with one virtual table variable (or without variables)?

Sincerely,

Nicolas

[v-jayw-msft · Accepted solution]

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

[amitchandak]

@nicolas23498324 , for rank, refer sub category rank

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://youtu.be/DZb_6j6WuZ0

[nicolas23498324]

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]
    )
)