Forum Discussion
Displaying top x with Rankx
=RANKX(ALL(Products), SUMX(RELATEDTABLE(InternetSales), [SalesAmount]))
You want to put a formula similar to that in a custom column. So, after you import the data into your data model, go to the middle icon on the left nav pane (the one that looks like a table) and then choose your data table, then choose the "Modeling" tab at the top and select "New Column". Paste in the formula. What the formula from the reference is doing is:
ALL(Products) - Gets rid of any context filters in order to make sure that all products are ranked. You may not want this in your situation. ALL is a filter function in DAX. Products is the name of the table that we want to create a ranking on. You would create this custom colum in the Products table. Substitute your own table name for Products.
SUMX(RELATEDTABLE(InternetSales), [SalesAmount])
This is using a related table, InternetSales and the column called "SalesAmount" from that table in order to do the ranking. In the example, InternetSales table contains transactions of product sales, so we have to sum them together. SUMX is a DAX function and allows you to adjust the context of the SUM function, in this case essentially switching tables to pull back rows from InternetSales that are related based upon the Product name/id. What is not provided here is that there is obviously some relationship between the Products table and the InternetSales table, likely based upon a product ID field.
Hope this helps. If you can post some sample data, could probably give you a better solution that is specific to your use case.
- michaeljc7010 years ago
Advocate II
Thanks for the response.
I did play around some more and discovered that I cannot use a custom formula because I am using DirectQuery. It seems I have to incorporate the rankings in my query. Please correct me if I am wrong.
- Greg_Deckler10 years ago
Community Champion
You are correct.