Syntax
RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
Tutorial with example
Learn how to use the DAX RANKX() function to arrange your data based on criteria that you specify.
Keynotes:
– Rankx on categories 03:00
– Rank ascending 03:17
– Rank with allselected 03:35
– Rank with blanks 05:28
– Rank on products 07:26
– Rank with column 10:50
Rank with a slicer:
In this video you will learn how to create a custom slicer that will rank your data based on the criteria that you specify, for example, Top 3, Top 5 and Top 10.
Break Rank ties:
In this video I will show you how DAX RAND function works and also how to use it to break ties on the RANKX calculation to be able to create TOP X lists in Power BI.
Dynamic TopN+Others
In this video we will use the dax function RANKX to create a dynamic TOP N elements and categorize the rest as others.
Download example file:
Link to demo file here.
Remarks
- If expression or value evaluates to BLANK it is treated as a 0 (zero) for all expressions that result in a number, or as an empty text for all text expressions.
- If value is not among all possible values of expression then RANKX temporarily adds value to the values from expression and re-evaluates RANKX to determine the proper rank of value.
- Optional arguments might be skipped by placing an empty comma (,) in the argument list, i.e. RANKX(Inventory, [InventoryCost],,,”Dense”)
Hi Ruth, I couldn’t download the example files. Get error messages like the following:
The webpage at https://degofile.re.cs.quickconnect.to/2kEOD/sharing/wC7Ep6s6w?tunnel=/tunnel/ might be temporarily down or it may have moved permanently to a new web address.
Can you post where the example files are located?
Your videos are awesome. So well constructed and your explanations are great. Keep adding more content.
All the links are working for me, can you try again with another browser? I need to move my download files somewhere else…
/Ruth
Hello Ruth, unfortunately , I couldn’t download the example files too, the link didn´t works
I have a table with Store ID, Store Name and Sales.
I have created a measure to Rank the Store Names based on highest sales.
StoreRank = RANKX(ALLSELECTED(
‘Table'[Store Name]),
CALCULATE(SUM(‘Table'[Sales])),,DESC)
When I use the Rank alone with Store Name it works, but the moment I add store ID in the it returns all ranks to – 1
Hi,
Could you please post the question in the power bi community? Thanks!