Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sroemer26
New Member

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

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
)
 
sroemer26_0-1718139766598.png

 

 
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!
 
sroemer26_1-1718139766599.pngsroemer26_2-1718139785017.pngsroemer26_3-1718139842098.png

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

5 REPLIES 5
vicky_
Super User
Super User

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.

vicky__0-1718145088698.png

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

 

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

 

sroemer26_0-1718200169944.png

 

ExcelMonke
Solution Supplier
Solution Supplier

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
Super User
Super User

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

That worked! Thank you for your wisdom AlexisOlson! 

 

sroemer26_0-1718202781795.png

wizard.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.