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.

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'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

3 Replies

  • 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...!

    • lhstrategy's avatar
      lhstrategy
      Regular Visitor
      7 years ago

      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