Group category by top 5

[michaelsh]

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

[mahoneypat · Accepted solution]

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

[michaelshparber]

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!

[mahoneypat]

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

[michaelshparber]

@mahoneypat 

Excellent, Pat!

I appreciate this a lot!

Have a great week!

Michael

[amitchandak]

@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

[harshnathani]

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!

[michaelsh]

@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

[harshnathani]

Hi @michaelsh ,

Can you share some sample Data and expected output?

Regards,

Harsh Nathani

[michaelshparber]

@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