Ranking Values with Measures

Using DAX RANKX
Using DAX RANKX

In many reports we produce we often need a method to score or rank data.  For example, we may need to list the sales totals for the sales team and rank them from highest sales to lowest sales.  Ranking can be done as a calculated column, or as a measure.  When using a measure, the ranking becomes dynamic and takes on the filter context of the table, or visual, that is showing the data.  Calculating a rank as a measure can be useful if you want to allow the user to select different categorical values such as product type and then have the report automatically rank the selected items.  When the report filter context changes the items are automatically re-ranked.

Alright, let’s jump into the data!

Open PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query.  Click Connect to open the Query Editor.  On the View ribbon click the Advanced Editor button.  While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor.  This tutorial teaches you how to copy and paste M code into the Advanced Editor.

let
  Source = Excel.Workbook(Web.Contents("https://powerbi.tips/wp-content/uploads/2017/05/Clothing-Sales.xlsx"), null, true),
  ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}})
in
  #"Changed Type"

Once you have copied the m code above into the query editor click Done.

Clothing Sales Data
Clothing Sales Data

Be sure to name your query Clothing Sales.  Then on the Home ribbon click Close & Apply to load the data into the data model.

To understand how the ranking will work we must first understand the DAX function ALLSELECTED.  You can read more about the Microsoft documentation on this function here.

To illustrate how the ALLSELECTED() function works we will make two measures and place them in a simple table.

Begin by creating a sum of the Sales in the Clothing Sales table.  Click New Measure on the Home ribbon.  Enter in the following measure equation:

Total Sales =  SUM ( ‘Clothing Sales'[Sales] )

Now, create a Table visual with the selected columns shown in the image below.

Add Table Visual
Add Table Visual

Sweet, we can see that all the categorical items have been added together forming totals.  Add the Slicer visual for the Category column, see example below.

Add Slicer
Add Slicer

Once the slicer is added we can select various items and see our table filter correctly.

Using the Slicer
Using the Slicer

Note: if you want to select multiple items in the slicer, hold the ctrl key and click on the multiple items that you want to select.  This is how I selected the multiple items in the image above.

Now, let us make a measure doing the same calculation but this time we will apply the ALLSELECTED() DAX function.  Click on New Measure on the Home ribbon and enter the following DAX formula.

Total Sales ALLSELECTED = CALCULATE( sum( ‘Clothing Sales'[Sales] ) , ALLSELECTED( ‘Clothing Sales’ ) )

Add this new measure into our existing table.

AllSelected Filter Context
AllSelected Filter Context

In this new formula we are calculating the sum of all the clothing sales but using the filter context of all the items selected from our filters.  Notice with nothing selected in our slicers that the sum of all Total Sales 55k, is the same for each row of the table for the column Total Sales ALLSELECTED.  This is due to the fact that we changed the filter context for the sum calculation.

Select Jeans and Pants from the slicer.  Notice we have the same results but with different totals.  The totals calculated using ALLSELECTED ignored the filter context of jeans and pants and calculated the total of all the selected sales.

Select Jeans and Pants
Select Jeans and Pants

Finally, we will now add the Ranking.  To calculate the rank we use the DAX function RANKX().  More documentation can be found on RANKX here.

Create a new measure and add the following:

Ranking = RANKX( ALLSELECTED( 'Clothing Sales'[Category] ) , CALCULATE( SUM( 'Clothing Sales'[Sales] ) ) )

Add the new measure, Ranking, to the table visual.  Ta Da, automatic ranking based on information that was selected from our slicer visual.

Adding Ranking Measure
Adding Ranking Measure

Note: when we used the RANKX function we called out a specific column the Category column from our Clothing Sales table.  If you only specify the table name this measure will not work.  We are using the filter context of the categories to conduct the ranking operation. 

 

13 Comments

  1. Hello,
    Is here a way to do a PERCENTRANK.INC instead of just ranking things 1..1000 and so on? For example, I have a list of ~200 vendors and I want the RANKX to reflect not just raw numbers but the distribution of the vendors total sales. I was able to do it in Excel easily enoguh, but don’t know how to duplicate it in BI. I got your ranking to work though!

    • One of the options of using Rankx as a measure is that it dynamically changes based on the data that is selected. We are able to change the filter context and the Rank adjusts. A Percent of total is more complex as you have to supply the rank of the row, as well as a total for the row to produce the % of total.

      Based on this I would likely try to push this up into the Query Editor, as this DAX gets expensive in terms of memory usage.

  2. Very cool! I have a question though, I have a date column and i would like to add a rank for every occurrence of a change in the date, is this possible?

    • I am not aware of such a function. This sounds to me that if it was possible, this would be quite complex for DAX. If I wanted to make multiple comparisons across different rows, I would try to work out a solution in either the source data (such as SQL) or in the Query Editor by making some custom functions. What I have found is that when you are doing more complex DAX expressions, sometimes working harder in the Query Editor to simplify the data or flatten out the schema will make your life much easier.

  3. Hi. I am looking to rank one of my columns based on the number of X values in another column on that row, can anyone help please?
    The scenerio – I have rows in my data which are questions about activities that have been completed and we therefore have an “answer” column. One of the columns in the table is the “store” the activity happened in. I want to be able to rank the store based on how many “no” values appear in the “answer” column. So if one store has 5/10 questions with “no” then they will be ranked above another store who has 7/10 no’s.
    Is this even possible?

    Failing that if I have a matrix in report view which shows me the list of stores and the % of yes and no responses how do I sort by the “no” column so that the ones with the highest proportion of “no'” appear at the top?

    Any help appreciated!

    • If I were approaching this problem, I would modify the query in the query editor. Step 1, change the yes to 1s and the nos to 0s. Then at the end I would create a column called total responses. In this column I would add up all the Yes values. This would produce for me a number from 0 to 10 of the number of yes answers. The higher the score the higher the yes, and thus rank the store at a higher level.

  4. Very helpful. I was stumped until I followed these exact steps.

  5. Hi there, this was helpful. Just in addition, how do can I change the ranking order? I.e. Highest value – lowest ranking.

  6. Hi All –
    I am trying to make a dashboard with trends and matrix table using rank.
    I am done creating top 2, top 4 etc.. now, Once I click the top 2 top customer will show perfectly my question how do I display those top 2 customer result including their historical data on month on month to make a trend and display in matrix table using power bi. Please help.. thank you

  7. Hi Mike – this exercise works fine. But the moment I replace my data I get the count of the ranking against the category (or in my case called a typology) – can you help, please?

    There are 94 individual lines – 44 is neighbourhood types, and so on. Instead of ranking the 6 typologies from 1 – 6. It provides the count rather than the ranking?

2 Trackbacks / Pingbacks

  1. #Excel Super Links #110 – shared by David Hager | Excel For You
  2. Community posts and a Power BI birthday | Guy in a Cube

Comments are closed.