Most common location

[Anonymous]

Hi everyone,

I need to calculate a measure for a Stacked bar chart to show the top 5 most common locations that people get picked up from.
I have the total amount of trips and 12 locations that trips were made to.  Unfortunately I can't use a filter for the top 5 as all locations that are not top 5 need to be amalgamated into the group called "Other".
I’ve tried a formula below that but it only shows me one - the (first) most common location, I need the top 5 most common:

Location Top =

CALCULATETABLE(TOPN ( 1, VALUES ( [Pick Up Location] ), RANKX( ALL( [Pick Up Location] ), [Total Pick Ups],,ASC)))

[TomMartens · Accepted solution]

Hey @Anonymous ,

I guess what you are looking for is described in this article: https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/.

Hopefully, this provides the information to tackle your challenge.

Regards,

Tom