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.
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]))
Species | 2019 | 2020 |
Hammerhead | 1 | 4 |
Mako | 3 | 3 |
Oceanic Whitetip | 4 | 6 |
White | 7 | 5 |
Tiger | 2 | 1 |
Bull | 5 | 2 |
Porbeagle | 6 | 7 |
@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
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
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.
Dear friend @Anonymous
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |