Top n / rankx

[Gordonik]

Hi All
How to do the measure which will show top 5 brands + others?
For instance:
1. Dove
2. FA
3. Adidas
4. Palmolive
5. Playboy
6. others (all other brands)
Based on "Value"
Here is an example of data model
:

I have found a Rob Colie helpful post here:
http://www.powerpivotpro.com/2014/11/displaying-top-n-bottom-n-and-all-others/
Following his pattern made this:
TOP 5 v.3 :=
CALCULATE (
[SALES VALUE (LC)];
FILTER ( PC[Brand]; RANKX ( ALL ( PC[Brand] ); [SALES VALUE (LC)] ) <= 5 )
but have no idea why this doesn't work and return the error:
"

Semantic Error: The value for column ‚Brand in table PC’ cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for measure refers directly to a column without performing any aggregation – such as sum, average or count – on that column. The column does not have a single value, it has many values, one for each row of the table and no row has been specified"​

(as a reference see Rob Colie measure: [Top 1000 Customer Sales])
Why his measure works but my doesn't?
Appreciate any help

 

[Matt Allington]

It is impossible to answer this question without further information. You show 1 table - what is the table name? Are there other tables? If so, what are they called and how are they joined? What is the formula for [sales value (LC)]? I assume it is a measure - is that correct.

 

[Gordonik]

Let's make this simpler.
I am looking for a pattern how to do a dynamic top N ranking with "others" at the end.
For instance there are 100 brands in a source table. I need to show the list of top N brands but with "others" at the end:

1. Dove
2. FA
3. Adidas
4. Palmolive
5. Playboy
6. others (all other brands)

But i want this list to be filtered by Year, manufacturer, segment and many more.
Is this possible?