Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Ranking and filtering over row and columns

I'm trying to rank a specific dimension by year and show always the top 5 for each year, even if they haven't always been in the top 5... but I can't seem to get the filter to work.


For example using the following formula based upon a simple value field produces the following table below:

RankSpecies = RANKX(ALLSELECTED(Shark[Species]),CALCULATE([Value]))


Oceanic Whitetip46


If a rank is higher than 5 in one year, but under in another, I still want to see that species. i.e. In the table above Porbeagle wouldn't be visible at all but Oceanic Whitetip and White would be visible.

If I add the measure as a filter then only the rank for 2020 is kept, and 2019 is reranked according to the values available from that in 2020.
Can anybody help me with this?
Impactful Individual
Impactful Individual

Dear friend @Mako 
please give some type of similar dummy data and also show what desired output you want . May be i can help


Hi Sujit,


The data would look typically something like this, but obviously with thousands of records over mutliple years:




What I am trying to achive is to display the Top 5 value for each year, with the columns as year and the rows as species. If a species is out of the top 5 in any given year, but is within the top 5 in another year... then this species should be displayed along with the actual rank for that year.




Maybe TopN would be better, but I haven't been able to get that working either. I believe I need to somehow add into the formula that the ranking should also be over the year. This works with the previously provided formula if the Species and Year are both set as rows, but if they're split into rows and columns adding the measure as filter doesn't work.

Does that help?


Super User IV
Super User IV

@Mako , not very clear to me.

But can create year vs last year measure with a separate date/year table

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) //date can be year Table
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))


and create rank on those and filter this year and last year and filter this year on 5


rank filter example

Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))
Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )



For Rank Refer these links

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!


Thanks @amitchandak 


I have created a sample pbix (download), but please be aware that I actually connected to a Tabular Model and so use of calculated columns etc are restricted (apologies for omitting this in the original message). This report contains two tables, one with the measure filter applied and one without.

I have added two separate measures as you'd suggested, and this seems to have done the trick. However, I am now having a problem keeping the original ranking when applying a filter to a measure. The measures are as follows:


Rank2019 =
    RANKX ( ALLSELECTED ( Shark[Species] ), CALCULATE ( SUM ( [VALUE] ) ) ),
    Shark[Year] = 2019
Rank2020 =
    RANKX ( ALLSELECTED ( Shark[Species] ), CALCULATE ( SUM ( [VALUE] ) ) ),
    Shark[Year] = 2020
RankFilter =
IF ( OR ( [Rank2019] <= 10, [Rank2020] <= 10 ), 1, 0 )

For example, in the screenshot below, Oceanic Whitetip has an original rank of 13 for 2019, but after the filter is applied the rank appears to be reevaluated and is now 12. As required, you can see that both the Basking & Megamouth is filtered out of the 'With Rank Filter' as these have not been in the top 10 in either of the two years. 


In addition, do you know how I could sort the table by the rank of 2020? And also, how to remove the value for the Rank Grand Total, as 1 obviously does not make sense.


Many thanks


@Mako For removing the grand total use this:

Rank =
IF (
    HASONEVALUE ( Shark[Species] ),
    RANKX ( ALLSELECTED ( Shark[Species] ), CALCULATE ( SUM ( Shark[Value] ) ) )

For removing the filters use ALL instead of ALLSELECTED.


No idea on how to sort the visual without a calculated column  

Thank you,
Antriksh Sharma

Thanks @AntrikshSharma 

The Grand Total is removed with that formula, but unfortunately adding ALL instead of ALLSELECTED doesn't display the original RANK prior to the filter.

Helpful resources

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!


Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors