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
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!
Blog
Twitter
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!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

@OwenAuger  Thanks for the solution, it worked flawlessly. How do I modify this to get the BOTTOM N?

Anonymous
Not applicable

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!
Blog
Twitter
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!
Blog
Twitter
LinkedIn

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.