Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alonardi
Frequent Visitor

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!

5 REPLIES 5
Phil_Seamark
Microsoft Employee
Microsoft Employee

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
        )

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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. 

 

Screenshot_1.png

 

 

 

 

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.  

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors