Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
konradjonsson
Helper II
Helper II

How to combine rankx, topn, and previous year

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.

Power BI - rankx, topn, prev year - complete list.png

 

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.

Power BI - rankx, topn, prev year - TopN-filter.png

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

The Revenues L12m is correct.

Power BI - rankx, topn, prev year - topn = 20.png

 

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]

 

2 REPLIES 2
mangaus1111
Solution Sage
Solution Sage

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.

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.