Forum Discussion
Select Top N over multi quarter period
I am trying to use the RANKX function to select top the top 5 vendors based on market share percet over the last several quarters. I have been able to select the top 5 vendors by quarter however the top 5 vendors often change quarter to quarter so I get inconsistent trend lines. Below are the RANKX and IF statement I am using:
TopRank = RANKX(ALLSELECTED(Vendor[NEWBrand]),[Market Share %],,DESC,Skip)
Top Vendors = IF([TopRank]<=5,[Market Share %])
What I would like is to select the top 5 vendors from the most recent quarter and then show historical data trends for those top vendors. Any help or suggestions would be greatly appreciated. Thank you.
Hi ConnerW,
I'm not 100% sure of the tables in your data model - so the below may well need to be adapted!
But for the sake of illustrating an idea, I'll assume all relevant columns are in your Vendor table:
- NewBRAND (dimension over which to calculate top 5)
- Quarter (some sort of index indicating the quarter - If you have a calendar table this will be different)
Here's a measure that worked in a dummy model I built:
= CALCULATE ( [Market Share %], KEEPFILTERS ( CALCULATETABLE ( TOPN ( 5, ALLSELECTED ( Vendor[NEWBrand] ), [Market Share %] ), CALCULATETABLE ( LASTNONBLANK ( Vendor[Quarter], 0 ), ALL ( Vendor ) ) ) ) )The part marked in red defines the 'final quarter' and would be different if you have a calendar table.
Putting this measure in a table/chart with Vendor/Quarter will show [Market Share %] only for those vendors who are in the top 5 in the last quarter with data. Also if you have any other filters applied, the top 5 will be subject to those filters.
Explanation:
The outer CALCULATETABLE(...) call generates a table of top 5 Vendors in the final quarter. Within this CALCULATETABLE:
- CALCULATETABLE( LASTNONBLANK(...)...) creates a filter context for the final quarter (assuming Quarter is simply a column of the Vendor table - adjust as needed). This will be different if you are working with a calendar table, depending how you define last quarter. As long as it generates a table of dates or whatever defining the last quarter it should work.
- TOPN(5,...) gets the top 5 Vendors (of those selected) within the context of the final quarter.
KEEPFILTERS ensures the above top 5 Vendors are intersected with the current filter context, so the result of the outer CALCULATE will be blank unless the current vendor is one of the top 5.
Hopefully that helps even if it needs to be modified for your model.
Owen :)
6 Replies
- OwenAuger
Super User
Hi ConnerW,
I'm not 100% sure of the tables in your data model - so the below may well need to be adapted!
But for the sake of illustrating an idea, I'll assume all relevant columns are in your Vendor table:
- NewBRAND (dimension over which to calculate top 5)
- Quarter (some sort of index indicating the quarter - If you have a calendar table this will be different)
Here's a measure that worked in a dummy model I built:
= CALCULATE ( [Market Share %], KEEPFILTERS ( CALCULATETABLE ( TOPN ( 5, ALLSELECTED ( Vendor[NEWBrand] ), [Market Share %] ), CALCULATETABLE ( LASTNONBLANK ( Vendor[Quarter], 0 ), ALL ( Vendor ) ) ) ) )The part marked in red defines the 'final quarter' and would be different if you have a calendar table.
Putting this measure in a table/chart with Vendor/Quarter will show [Market Share %] only for those vendors who are in the top 5 in the last quarter with data. Also if you have any other filters applied, the top 5 will be subject to those filters.
Explanation:
The outer CALCULATETABLE(...) call generates a table of top 5 Vendors in the final quarter. Within this CALCULATETABLE:
- CALCULATETABLE( LASTNONBLANK(...)...) creates a filter context for the final quarter (assuming Quarter is simply a column of the Vendor table - adjust as needed). This will be different if you are working with a calendar table, depending how you define last quarter. As long as it generates a table of dates or whatever defining the last quarter it should work.
- TOPN(5,...) gets the top 5 Vendors (of those selected) within the context of the final quarter.
KEEPFILTERS ensures the above top 5 Vendors are intersected with the current filter context, so the result of the outer CALCULATE will be blank unless the current vendor is one of the top 5.
Hopefully that helps even if it needs to be modified for your model.
Owen :)
Thanks Owen great suggestions works beautifully! One more question on the topic. Is there a way to combine vendors that do not make it into the top 5 and display them in a single grouping? For instance you would have 5 named competitors and then a "Other" competitor which is just the sum of the other vendors not in the Top 5?
- OwenAuger
No problem :)
My preferred method for TopN & Other is to create an Other measure that only displays at a total level, then create a two-row table containint Top & Other.
First of all, to invert the Vendor filter, I would use EXCEPT:
Vendors except Top 5 in Latest Quarter
= CALCULATE ( [Market Share %], KEEPFILTERS ( EXCEPT ( ALLSELECTED ( Vendor[NEWBrand] ), CALCULATETABLE ( TOPN ( 5, ALLSELECTED ( Vendor[NEWBrand] ), [Market Share %] ), CALCULATETABLE ( LASTNONBLANK ( Vendor[Quarter], 0 ), ALL ( Vendor ) ) ) ) ) )Then, I would create a measure that only displays when Vendor is not filtered:
Vendors except Top 5 in Latest Quarter Total = IF ( NOT ( ISFILTERED ( Vendor[NEWBrand] ) ), [Vendors except Top 5 in Latest Quarter] )Then have a look my other post for an example of a pbix file with a Top/Other table and a measure that switches between Top/Other measures. You may need to experiment with how to lay this out, especially if putting this in a chart.
Owen :)
OwenAuger Thanks for the solution, it worked flawlessly. How do I modify this to get the BOTTOM N?
