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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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

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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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