RANKX Grouping

[alonardi]

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!

[Phil_Seamark]

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]

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]

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. 

[Phil_Seamark]

Hi @alonardi

When you use the UNION function, to joins two tables together, one on top of the other.  Both tables need to have the same number of columns and I'm guessing this is the problem here.  So it is probably just a case of changing the logic in the VAR OtherSales line to match.  

[alonardi]

Yes, sure Phil. I use unions and joins frequently, but I don´t work with variables. 

I don't understand which result will each var line, so I'm disabled to modify the structure of the OtherSales line. 

Can u please see the screenshot I upload, so you can light me in my darkness? 

Thank you so much.