Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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:
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:
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 🙂
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:
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:
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 🙂
@OwenAuger Thanks for the solution, it worked flawlessly. How do I modify this to get the BOTTOM N?
Never mind. Pass ASC to the TOP N as the parameter 🙂
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?
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 🙂
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |