cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ConnerW
Regular Visitor

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.

1 ACCEPTED SOLUTION
OwenAuger
Super User
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 🙂

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
Super User
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 🙂

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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

That's the one, glad it's working 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

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.

 

Post on top N / Other

 

Owen 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!