Power Pivot Principles: Introducing the RANKX Function

Welcome back to our Power Pivot blog.  Today, we show you how use set up slicers to dynamically segregate text data based on text inputs.

The RANKX function returns with the ranking of the number values in a column in a table.  It uses the following syntax:

The RANKX requires a <table> and an <expression> input.  The <value>, <order>, and <ties> inputs are optional.

For this example, we are going to look at the following table, note that this table (an Excel Table) is given the name ‘ProductListKitchen’ in Excel: 

Imagine that we wanted to rank the products in this Excel Table from the highest to lowest price in Power Pivot.  Here we may use the RANKX function.

After adding the Table to the data model, we can create a calculated column with the following DAX code:

The <table> input in this code is our table ‘ProductListKitchen’, because this is the Table that we wish to evaluate.  The <expression> is the column that contains the values that we wish to be ranked, and that would be the Price column.

The resulting calculated column will then look like this: 

We can then sort the items:

The default ranking is applied in decending order where RANKX ranks the product type with the highest price first.  To swap the ranking order around we have to add ‘ASC’, which is short for ascending, as the <order> input:

The resulting table will now look like this: 

There we have it, we have ranked our products by ascending price in our data table.

That's it for this week, come back next week for more Power Pivot. Until then happy pivoting!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about >here. If you wish to catch up on past articles in the meantime, you can find all of our past Power Pivot blogs here.