Forum Discussion
RANKX Grouping
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
)
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.
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.
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.