How to combine rankx, topn, and previous year

[konradjonsson]

Hi.

I have created a ranking table of our markets, based on last 12 month's revenues.

I have also included a comparison (rank change) with previous year's ranking.

The challenge I have not yet resolved is how to maintain the correct rank change vs previous year, when I apply a TopN-filter on the table. I apply the TopN-filter using the Filters pane.

See below - for example, Market A and B are no longer showing 0 in Rank Change vs PY.

The Revenues L12m is correct.

Any ideas on how to resolve this?

DAX measures below.

Ranking on Market =

IF(

    ISINSCOPE('2.1 Market'[MarketName]),

    RANKX(

        ALLSELECTED('2.1 Market'[MarketName]),

        [YTD]

    )

)

Ranking on Market PY =

IF(

    ISINSCOPE('2.1 Market'[MarketName]),

    RANKX(

        ALLSELECTED('2.1 Market'[MarketName]),

        [PY YTD]

    )

)

Rank Change vs PY =

[Ranking on Market PY] - [Ranking on Market]

[mangaus1111]

Hi @konradjonsson ,

it is difficult for me to say where the problem is without using your real database.

I have tried with a sample database build by myself, but it seems that your measures are all correctly created.

Maybe you can try to substitute ALSELECTED with ALL and see what happen.

[konradjonsson]

Hi @mangaus1111 

Thank you for suggestion.

Replacing ALLSELECTED with ALL did however not solve the problem.

I have seen a video (from Marco Russo at sqlbi) where the TopN-function is built into a measure (instead of using the Filters pane). I will try and see if it helps.