Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
murrayb8
Helper I
Helper I

Showing Top N values per category based on a value

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:

 

ProductPhraseValue
Product #1One Phrase234
Product #1Two Phrase342
Product #1Three Phrase23
Product #1Four Phrase346
Product #1Five Phrase745
Product #2One Phrase234
Product #2Two Phrase342
Product #2Three Phrase23
Product #2Four Phrase346
Product #2Five Phrase745
Product #3One Phrase234
Product #3Two Phrase342
Product #3Three Phrase23
Product #3Four Phrase346
Product #3Five Phrase745

 

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:

ProductPhraseValue
Product #1Five Phrase745
Product #1Four Phrase346
Product #2Five Phrase745
Product #2Four Phrase346
Product #3Five Phrase745
Product #3Four Phrase346

 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!

1 ACCEPTED SOLUTION
murrayb8
Helper I
Helper I

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.

View solution in original post

4 REPLIES 4
fidelv
New Member

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
Helper I
Helper I

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
Community Support
Community Support

@murrayb8,

 

You may simply use Visual level filters Top N.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.