How to rank / sort when you have two variables on one axis

[sroemer26]

Hey guys,

So I figured I would put my data into a table to better visualize how I am trying to rank it. 

Currently, I have 

USD_Scrap_Rank =

RANKX(

    ALLSELECTED('Analysis EXCEL'[NCR Item No]),

    [Sum_Of_USD_Scrap],

    ,

    ASC

)

This is close but not what I want. It is ranking my top 6 as 1,2,3,4,5,6 and so on for each month. What I want it to do is in the 'innerrank' column on my xcel sheet. If you have any suggestions for me I would appreciate it!

[vicky_]

You need to include the month in the allselected section; Here's my revised DAX:

Scrap Rank = RANKX(
    ALL(Data[Month], Data[NCR ITEm]), 
    CALCULATE(SUM(Data[USD Scrap]))
)

You can tweak it as needed.

BTW - i just rounded the scrap amounts to the lowest 100, so that's why there's ties in my screenshot.

[sroemer26]

hmm... Not sure if I implemented your solution correctly?

[ExcelMonke]

Hmmm this is quite interesting and complex... this is not the cleanest of solutions, but consider the following:

You can create a "helper" column that indexes your months, where your starting month index is 0. Then consider the following measure:

USD_Scrap_Rank =
VAR _Rank =
    RANKX (
        ALLSELECTED ( 'Analysis EXCEL'[NCR Item No] ),
        [Sum_of_USD_Scrap],
        ,
        ASC
    )
VAR _Index = 
	'Table'[IndexHelperColumn] * 6
RETURN
    _Rank + _Index

Essentially, this will add the continuous counting of your rank, like how you wanted in your inner rank. 

[AlexisOlson · Accepted solution]

I'd recommend using the RANK function instead of RANKX. It's easier to sort by multiple things.

https://www.sqlbi.com/articles/introducing-the-rank-window-function-in-dax/

Your case might look something like this:

USD_Scrap_Rank =
VAR SourceTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Analysis EXCEL',
            'Analysis EXCEL'[NCR Item No],
            'DateTable'[Year],
            'DateTable'[Month],
            "@Sum", [Sum_Of_USD_Scrap]
        ),
        ALLSELECTED ( 'Analysis EXCEL'[NCR Item No] ),
        ALLSELECTED ( 'DateTable' )
    )
VAR Result =
    RANK (
        DENSE,
        SourceTable,
        ORDERBY (
            'DateTable'[Year], ASC,
            'DateTable'[Month], ASC,
            [@Sum], ASC
        )
    )
RETURN
    Result

[sroemer26]

That worked! Thank you for your wisdom AlexisOlson!