cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Group category by top 5

In my Products table i have two columns - Product and Category. It has thousands of products and 50 categories. I would like to add a calculated column - “Top5Category”. It will return the Category name if the Category is one of top 5 categories by the number of products. If it is not in top 5 then it will return “Other” Please help Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Group category by top 5

Please try this expression in your calculated column to get your desired result.

 

Report Category =
VAR __thiscategory = Products[Category]
VAR __thiscategorycount =
    CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
VAR __summary =
    ADDCOLUMNS (
        SUMMARIZE ( Products, Products[Category] ),
        "@productcount", CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
    )
VAR __thiscatrank =
    RANKX ( __summary, [@productcount], __thiscategorycount, DESC, SKIP )
RETURN
    IF ( __thiscatrank <= 5, "C" & __thiscatrank, "Other" )

I wasn't clear if the column you gave was an example of expected output or not.  If you want it to say "Top5Category", just replace the true term in the IF() with that text instead of "C" & __thiscatrank.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Highlighted
Super User V
Super User V

Re: Group category by top 5

Hi @michaelsh ,

 

https://community.powerbi.com/t5/Desktop/Top-5-by-Category/m-p/754307

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

 

 


Regards,

Harsh Nathani


Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

 

 

 

Highlighted
Super User IX
Super User IX

Re: Group category by top 5

@michaelsh , refer if these can help

https://community.powerbi.com/t5/Desktop/Top-5-and-others/td-p/165945

https://www.youtube.com/watch?v=UAnylK9bm1I



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Group category by top 5

@harshnathani @amitchandak 

This is not my issue. I went through a lot of RANKX TOPN articles before posting this question. 
My challenge is:

For every product - to calculate the rank of ITS CATEGORY. 
NOT the rank of a product within category, but the rank of a CATEGORY by the number of products. 
Thanks

Highlighted
Super User V
Super User V

Re: Group category by top 5

Hi @michaelsh ,

 

Can you share some sample Data and expected output?

 

Regards,

Harsh Nathani

Highlighted

Re: Group category by top 5

@harshnathani 

Here is the file:

https://1drv.ms/u/s!AoP_9ampPIT77kP8Eh0-vFgwvKof?e=PQ65gP

You can see in the picture that I am trying to create a calculated column - ReportCategory that shows only top 5 categories (C1-C5).

For this I need to calculate number of products for each category (hand written in green), rank the categories and then create "if rank <=5 then category else "Others"

Something like that

Please help

Thanks a lot

 

Annotation 2020-07-08 003048.png

 

Highlighted
Super User VI
Super User VI

Re: Group category by top 5

Please try this expression in your calculated column to get your desired result.

 

Report Category =
VAR __thiscategory = Products[Category]
VAR __thiscategorycount =
    CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
VAR __summary =
    ADDCOLUMNS (
        SUMMARIZE ( Products, Products[Category] ),
        "@productcount", CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
    )
VAR __thiscatrank =
    RANKX ( __summary, [@productcount], __thiscategorycount, DESC, SKIP )
RETURN
    IF ( __thiscatrank <= 5, "C" & __thiscatrank, "Other" )

I wasn't clear if the column you gave was an example of expected output or not.  If you want it to say "Top5Category", just replace the true term in the IF() with that text instead of "C" & __thiscatrank.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

Re: Group category by top 5

Excellent! It is working.

Thanks a lot @mahoneypat !

Could you please explain how is it working?

If you have a link to a similar solution - great

Thanks!

Highlighted
Super User VI
Super User VI

Re: Group category by top 5

Glad it is working for you.  Here is the expression with comments to explain how it works

 

Report Category =
VAR __thiscategory = Products[Category] //store this category in a variable
VAR __thiscategorycount =
    //calculate the count for this category removing all filters except for the category
    CALCULATE (
        COUNTROWS ( Products ),
        ALLEXCEPT ( Products, Products[Category] )
    )
VAR __summary =
    //make a virtual table of all the categories with their respective counts
    ADDCOLUMNS (
        SUMMARIZE ( Products, Products[Category] ),
        "@productcount", CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
    )
VAR __thiscatrank =
    // find the rank for this category (using the variable as 3rd term in RANKX)
    RANKX (
        __summary,
        [@productcount],
        __thiscategorycount,
        DESC,
        SKIP
    )
RETURN
    IF ( __thiscatrank <= 5, "C" & __thiscatrank, "Other" )
//if <=5 concat the rank with C otherwise put "Other"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Re: Group category by top 5

@mahoneypat 

Excellent, Pat!

I appreciate this a lot!

Have a great week!

Michael

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors