This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Explaining the VALUE argument of the RANKX function in DAX
The RANKX function does what the name implies - it ranks values based on a calculation. However, the VALUE argument often causes confusion; this blog explains what it does!

Posted by Sam Lowrie on 28 October 2019

Using RANKX in DAX

The RANKX function is handy when trying to find (say) the highest (or lowest) performing product or company. In this case here are the product sales for our fictional Create-A-Creature retail chain:

The Habitat is where the sold toy lives (for example Faye the Fox lives in the Urban habitat whilst Cleopatra the Clownfish lives in Salt water).

To make things easier I've created a measure to work out all the sales in 2017 and will rank each Habitat based on its sales.

Sales in 2017 =

--Change the filters being applied to a given expression

CALCULATE(

--SUM all the sales made

SUM(Purchase[Quantity])

,FILTER(

-- Filter the calendar table to include only dates in 2017

'Calendar'

-- This will filter the purchases table to show only purchases in 2017

,'Calendar'[YearNumber] = 2017

)

The RANKX function will perform this measure for every row of a given table and order them 1 to n based on the sort direction we indicate:

2017 Rankings =

RANKX(

-- The table to rank over (ALL removes any filters)

ALL(Habitat)

-- The calculation to use for ordering

,'All Measures'[Sales in 2017]

-- We will come back to the VALUE argument, but for now it can be left blank

-- The direction of the sorting (Z-A or Largest to Smallest)

,DESC

--How to handle ties (Dense: 1st, 1st, 2nd or Skip: 1st, 1st, 3rd)

,Dense

)

Time to find out what this looks like in our visual:

The total shows as rank 1, but this can be removed with a simple IF function, testing whether HASONEVALUE(HabitatName) is true or not.

As with any measure this is affected by filter context, so any slicers that change the sales involved may also lead to a change of rankings.

The VALUE argument

One of the more confusing arguments around, the VALUE argument can replace which numbers Power BI compares your expression value to. Often it can be left blank:

By default the result of the current row is compared to the result of this expression for every other row. Sky (for example) is the third largest, so the ranking shows as 3.

Using Value would allow us to compare the result of this expression with a different set of numbers.

For example this is the same measure, but working out the sales for the previous year.

Sales for Sky (961) placed it 3rd against the other habitats in the same year, but how would this compare to the 2016 sales?

The sales of 2017 for Sky (961) would have placed it 2nd compared to the 2016 sales.

This is done by changing the expression to work out for every row the 2016 sales, then seeing where the current row's 2017 data would rank compared to these figures:

2017 vs 2016 Rankings =

RANKX(

-- The table to rank over (ALL removes any filters)

ALL(Habitat)

-- The expression to work out for each row in the table

,'All Measures'[Sales in 2016]

-- Compare this value to each row's value for the above calculation

,'All Measures'[Sales in 2017]

-- The direction of the sorting (Z-A or Largest to Smallest)

,DESC

-- How to handle ties, Dense: 1st, 1st, 2nd or Skip: 1st, 1st, 3rd.

,Dense

)

Value could also be a static number such as 1000, or a SWITCH that returns a different number depending on the week of the month, etc.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using RANKX in DAX

The VALUE argument

Head office

London

Manchester