cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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]))

 

Species20192020
Hammerhead14
Mako33
Oceanic Whitetip46
White75
Tiger21
Bull52
Porbeagle67

 

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?
6 REPLIES 6
Highlighted
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

Highlighted

Hi Sujit,

 

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

 

Mako_0-1598431914077.png

 

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.

 

Mako_1-1598432431315.png

 

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?

 

Highlighted
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

https://databear.com/power-bi-dax-topn-function/
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...



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!

Highlighted

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 =
CALCULATE (
    RANKX ( ALLSELECTED ( Shark[Species] ), CALCULATE ( SUM ( [VALUE] ) ) ),
    Shark[Year] = 2019
)
Rank2020 =
CALCULATE (
    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

Highlighted

@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
Highlighted

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

Announcements
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!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors