Help required with RANKX

DAX DAX Calculations
1

Hi there,

I am struggling to get RANKX to work as expected and would gratefully appreciate some help…

Please see attached PBIX file, which has two tables: Location and Sales: https://www.dropbox.com/s/mxg2qb0c4ejcaio/Sample%20Top%20and%20Bottom.pbix?dl=0

Location is having 3 columns(county, City and Location code) there are multiple Cities under same county and multiple location code under same City)

Sales is having 3 columns(LocationCode, Product and Sales)

The problem I am facing here is I need to rank the top and bottom sales by County and City. However since there are multiple locations under same city the rankx function is not giving me the desired output. If I put column LocationCode into the table it works, but as soon as I remove it, it breaks.

This is the DAX I am using to calculate the Top Rank = 
    RANKX(ALLSELECTED(Sales),[Total Sales],,DESC) 

and for Bottom Rank = 
    RANKX(ALLSELECTED(Sales),[Total Sales],,ASC)

Additionally, on the 2nd tab ‘Matrix’, I would like to know how to get my RANKX measures to work, regardless of the hierarchy displayed, eg whether by County or by City.

Many thanks

Mark

2

Yes RANKX can be a difficult one.

I covered it in depth in this particular Learning Summit session here.

Definitely recommend working through this when you can.

I likely cover in here how to solve exactly this question.

Are you sure this is actually producing the correct result. It just doesn’t look like it to me.

image

This was a tricky one I had to work through a bit myself.

It’s just so important to understand the context of the calculation here. I thought I had it right in the formula initially but then realized that the location code was originally coming from the sales table.

I change it to the location table

image
image1638×547 75.4 KB

This was the formula

Top Rank = 
IF( ISBLANK( [Total Sales] ), BLANK(),
    CALCULATE(
        RANKX( ALL( Location[LocationCode] ), [Total Sales], , DESC ),
            ALL( Location ),
                VALUES( Location[LocationCode] ) ) )

image

See how you go working with this idea.

Sam