Showing Top N values per category based on a value

[murrayb8]

Hi I was wondering if someone could help me with a roadblock im hitting. I have a large data set that has multiple categories and I need to filter to the top N number of entries based on a value for each category. As currently I have about 60+ categories I need to have these categories in one table. I've had a look and found there are ways of filtering using RANX and TOPN but from what I've seen they dont tend to keep all the values in the same table, they just show a aggregation of the metric.

Example similar to my data:

Product	Phrase	Value
Product #1	One Phrase	234
Product #1	Two Phrase	342
Product #1	Three Phrase	23
Product #1	Four Phrase	346
Product #1	Five Phrase	745
Product #2	One Phrase	234
Product #2	Two Phrase	342
Product #2	Three Phrase	23
Product #2	Four Phrase	346
Product #2	Five Phrase	745
Product #3	One Phrase	234
Product #3	Two Phrase	342
Product #3	Three Phrase	23
Product #3	Four Phrase	346
Product #3	Five Phrase	745

And if I were to run a measure or calculated column to give me Top 2 Phrases per Product based on value I would ideally get the following table:

Product	Phrase	Value
Product #1	Five Phrase	745
Product #1	Four Phrase	346
Product #2	Five Phrase	745
Product #2	Four Phrase	346
Product #3	Five Phrase	745
Product #3	Four Phrase	346

 Ideally I would love to have this measure showing top N% of phrases per product based on sum of value per product but I think that might not be possible or be crazy heavy as a measure so am setting for the above. Any help on this would be greatly appreciated!

[fidelv]

I'm a beginner and I'm trying to find out the year the first time a customer bought (and total of all purchases that year).

What is my 'mytable' in this your example? I even tried renaming my sheet 'mytable'.

I am getting this error when I try your solution: Token literal expected

[murrayb8 · Accepted solution]

I managed to fins a way to do this thankfully:

Step #1: I went into query editor and sorted my table high to low by the value column and then added an index column called Index and applied changes.

Step #2: I added a calculated column called rank using this formula: Rank = RANKX(FILTER('mytable','myTable'[uniqueProductCode]=EARLIER('myTable'[uniqueProductCode])),'myTable'[Index],,ASC)

Step #3: I selected the modeling tab, clicked New Table and used the following formula for my new calculated table: 

Top 1000 Products =
SELECTCOLUMNS (
FILTER ( 'myTable', 'myTable'[Rank] <= 1000 ),
"Product", 'myTable'[Product],
"Phrase", 'myTable'[Phrase],
"UniqueProductCode", 'myTable'[uniqueProductCode],
"Value", [Sum of Value],
"Rank", 'myTable'[Rank]
)

I can then adjust the table as needed by changing "<= 1000" to represent the top N values per category. Hope this helps others if they are looking for the same solution.

[v-chuncz-msft]

@murrayb8,

You may simply use Visual level filters Top N.

[murrayb8]

Hi Sam thanks for your response, unfortunately that wont work for me as Im trying to get the Top N by category with all categories listed in the same table. I did happen to find the answer though so will post my solution for any who are also looking