How create rank in asymmetric matrix

[jmorav291]

Hi, community.

I'm working on an asymmetric matrix and I need to create one column that shown a dynamic rank. 

I created a measure that returns 3 columns: Information about one week, the second week, and the difference between one and the second week. But I need to know how can I create inside this measure a new column that shown a dynamic ranking and sort data ascending by this ranking column.

Here a sample of that table I created:

Please, I need your help to understand how I can develop the code for ranking inside this measure. Below my code:

dynamic_table_4 = 
    VAR _index = 
        SELECTEDVALUE(header_test[Index_col], BLANK())
        
    VAR _before_lw = CALCULATE(
        MIN(search_terms_db[week_ix]),
        ALLEXCEPT(search_terms_db,search_terms_db[week])
    )

    VAR _last_week = CALCULATE(
        MAX(search_terms_db[week_ix]),
        ALLEXCEPT(search_terms_db,search_terms_db[week])
    )

    VAR ranking_before_lw = CALCULATE(
        [weekly_ranking],
        search_terms_db[week_ix] = _before_lw
    )

    VAR ranking_last_week = CALCULATE(
        [weekly_ranking],
        search_terms_db[week_ix] = _last_week
    )

    VAR ranking_bw_NotBlank = 
        IF(ranking_before_lw <> 0,
        ranking_before_lw)
    
    VAR ranking_lw_NotBlank = 
        IF(ranking_last_week <> 0,
        ranking_last_week)

    VAR difference = IF(
        OR(
            ranking_before_lw = BLANK(),
            ranking_last_week = BLANK()
        ), 0,
            ranking_bw_NotBlank - ranking_lw_NotBlank
    )

    VAR difference_NotBlank = 
        IF(difference <> 0,
        difference)

    VAR ChangeRanking = difference_NotBlank
    VAR ranking = IF(
        HASONEVALUE(search_terms_db[Search Term]) && (ChangeRanking <> 0),
        VAR SearchTernsRank = 
            ADDCOLUMNS(
                ALLSELECTED(search_terms_db),
                "@SearchRank", RANKX(ALLSELECTED(search_terms_db), search_terms_db[Search Term], ,DESC,Dense)
            )
        VAR MaxSeartTermRank = 
            MAXX(SearchTernsRank, [@SearchRank])
        VAR LookupTable = 
            ADDCOLUMNS(
                SearchTernsRank,
                "@DifferenteRank", difference_NotBlank * MaxSeartTermRank + [@SearchRank]
            )
        VAR CurrentST = SELECTEDVALUE(search_terms_db[Search Term])
        VAR CurrentSTRank = 
            RANKX(ALLSELECTED(search_terms_db), search_terms_db[Search Term], CurrentST, DESC, Dense)
        VAR CurrentValue = ChangeRanking * MaxSeartTermRank + CurrentSTRank
        VAR new_ranking = 
            RANKX(LookupTable, [@DifferenteRank], CurrentValue, , Dense)
        RETURN
            new_ranking
    )

    RETURN

        SWITCH(
            _index,
            -1, IF(NOT ISBLANK(ranking_lw_NotBlank),
            ranking_bw_NotBlank),
            0, IF(NOT ISBLANK(ranking_bw_NotBlank),
            ranking_last_week),
            1,difference_NotBlank,
            2, ranking
        )

[lbendlin]

Remember that a measure is computed for every cell of your visual (including the subtotals and grand totals).  That means you need to recompute the ranking as well, each time, making sure you use the same sort criteria.