Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating Buckets based on Top N Numbers

Hi, I am trying to find out a solution for showing Top Numbers as in bucket format. Ex:- I have a column (Messages). I need to find out Top 5 messages , top 5 to 10 messages , top 10 to 15 messages.I need to create it from buckets as in my drop down says as Top Messages Top 5, Top 5 to 10, Top 10 to 15. As per my drop down selections i need my table chart to display the messages accordingly. Please suggest. 

 

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago
    Ranking = 
Var YourRank = RANKX(
    FILTER(Tble,
            Tble[Class]=EARLIER(Tble[Class])
    )
    ,Tble[Sl.No],,ASC
)
Return
SWITCH(
        TRUE(),
        YourRank<=5,"Top 5",
        YourRank>5 && YourRank<=10,"Top 5-10",
        YourRank>10 && YourRank<=15,"Top 10-15",
        "More than 15"
)

6 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous 

    Here's an idea. Create a calculated column:

    Bucket =
VAR Rank_ =
    RANKX ( Table1, Table1[Column to rank on],, DESC )
RETURN
    SWITCH (
        TRUE (),
        Rank_ <= 5, "Top 5",
        Rank_ <= 10, "Top 5-10",
        Rank_ <= 15, "Top 10-15",
        BLANK ()
    )

    You can then use that column to slice your data

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for your response, What exactly I am looking for is :

      Consider I have a table as below

      Sl.NoClassmessage
      1aabc
      2bxyz
      3cww
      4brr
      5sed
      6asgg
      7rthg
      8asc
      9bswa
      10chjy
      11cfs
      12asse
      13aew
      14ctr
      15but

       

      I need two drop downs 1.Class   2.TopN as (0 to5, 6to10,11 to15).

      When i choose Class a in first drop down and select 0to5 bracket in second drop down, my table has to show me only top 5 recordes of Class A.

      Hope i made it clear.

      please suggest

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Here the top 5 need to be decided by which cloumn, is there any seperate fact(like sales) for the same or top from the first column serial number?