How to use RankX function to rank a category based on values.

1.     First, we need to create an aggregation on which the ranking will be based on.

                    Revenue = SUM(Sales Table[Sales])

2.  Now that we have the values, we are ready to rank a category, in this case it’s customer name field.

                  Customer Rank = RANKX (ALLSELECTED(Customer [Name]), [Revenue],, DESC, Dense)

3.     In this formula, we used the ALLSELECTED function to specify that we want our values to be grouped by Customer name only, what ALLSELECTED does is that it ignores all the filters specified inside a query, but it will still apply the filters outside the query, like when we put the values in a table. We specified the Revenue for the ranking to base on and then we used DESC to sort it by descending revenue value (the highest revenue will be the lowest rank) and then we used Dense to not skip ranking the rows that have the same value. 

4.     Now we can put the result in a table next to customer and revenue and see that it is properly ranking the customers based on the revenue values.

 

Scroll to Top