Forum Discussion

alonardi's avatar
alonardi
Frequent Visitor
8 years ago

RANKX Grouping

Hi. How can I build a table which ranks the top 10 clients by sales of a sales table, and also show a final single row in the table which group all the sales of all the "others clients" not included in the top 10 listed rows above? The sum of the last single row will probably be higher than more than many of the ranked top 10 clients. Thanks a lot! for the help!

Need Help

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI alonardi

     

    One approach is to create a calculated table that returns 10 rows that happen to be your top ten.  Then union onto this calculated table the single row that summarizes the remaining rows.  This should be pretty easy to do.  If you post a sample set of data that resembles your table we can post suggestions.

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      In essense, if you start with a data set like this and call the 

       

      ClientID,Sales
      A, 100
      B, 200
      C, 300
      D, 400
      E, 500
      F, 600
      G, 700
      H, 800
      I, 900
      J, 1000
      K, 1100
      L, 1200
      M, 1300
      N, 1400

       

      You can create a calculated table using the following code

       

      New Table = 
Var Top10Sales =  TOPN(10,Sales,Sales[Sales])
var OtherSales =  SUMMARIZE(EXCEPT(Sales,Top10Sales),"ClientID","Other","Sales",SUM(Sales[Sales]))
RETURN 
    UNION(
        Top10Sales,
        OtherSales
        )

      • alonardi's avatar
        alonardi
        Frequent Visitor
        8 years ago

        Awesome Phil! The example is great!

        I replicated it easily, but when I try to use it in my dataset, I got an error of different numbers of columns of the Join. 

         

        My Table Name is "Agrupado Facturación" which replace the name "New Table" of your example.

         

        My Table is set with 4 fields like this: 

         

        Period, Category, ClientName; Sales

         

        The Real Names of the fields are (They are the same fields in Spanish):

         

        Fecha, Nombre Cuenta, Razon Social, Total Facturación 

         

        Then, I wrote the same code you use in your example, but de Union didn't work =(. Not good. Please let me know which changes I must do. I´m sorry, your explanation was really good.