Dynamic Grouping based on Rank

[Anonymous]

Hi Everyone,

I want to create group on top of States based up on count of coustomers.

where the rank of customers greater than 5 then it has to group together as 'Others' , rest diaply as it is.

The grouping should be dynamic based on Customers count.

Please find example below:

I have data like below Screen shot 1

And I have to create a group as below screen shot2 

Need your help,  Thanks,

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use below calculated column formula to achieve your requirement.

Logic:

1. Calculate current rank.

2, Add if statement to replace column name to other when rank greater than 5.

Categroy =
VAR summary =
    SUMMARIZE ( 'Table', [Type], "Count", COUNT ( [Index] ) )
VAR _rank =
    MAXX (
        FILTER (
            ADDCOLUMNS (
                summary,
                "Rank", RANKX ( summary, [Count], MAX ( [Count] ), DESC, DENSE )
            ),
            [Type] = EARLIER ( 'Table'[Type] )
        ),
        [Rank]
    )
RETURN
    IF ( _rank <= 5, [Type], "Other" )

Regards,
Xiaoxin Sheng

[lhstrategy]

Hi Xiaoxin,

I have a similar problem and your solution looks pretty neat.

Would you help me adapt your script to group [Type] by the share %?

In this example, if COUNT( [Index] ) as % of Total < 4% then "Other".

Many thanks!

Fiorenzo

[Anonymous]

It is working perfect as expected. Thanks much Xiaoxin Sheng...!