Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dynamic Grouping based on Rank

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....
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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