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