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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
amitchandak
Super User
Super User

@Anonymous , 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/367415

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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.

Sujit_Thakur
Solution Sage
Solution Sage

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

Anonymous
Not applicable

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?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors