Forum Discussion

Flynnk31's avatar
Flynnk31
Regular Visitor
9 years ago

RANKX on measure

Hi All,  I'm relatively new to PowerBI and moreso DAX.  I am having an issue trying to do a RANKX on a "Total_Cost" measure within one of my reports.  So what I have right now is a list of items per...
Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

Flynnk31

 

Is this the result you are looking for? If it is...

 

Rank Measure = RANKX( ALLEXCEPT('Table', 'Table'[No_]), [Total Cost Measure] ,, DESC)

 

 

EDIT: So you can also try something like this...

Rank Measure =
IF (
    HASONEVALUE ( 'Table'[lotNo] ),
    RANKX ( ALL ( 'Table'[lotNo] ), [Total Cost Measure],, DESC ),
    IF (
        HASONEVALUE ( 'Table'[No_] ),
        RANKX ( ALL ( 'Table'[No_] ), [Total Cost Measure],, DESC )
    )
)

And the results...

 

This should give you some ideas on how to approach this depending on your real data!

 

Hope this helps!

Good Luck! :smileyhappy:

Flynnk31's avatar
Flynnk31
Regular Visitor
9 years ago

Almost Sean, but thank you!

 

I actually got it exactly where I want it to be, except now it's just missing Rank 1 in the live data, where in my data from Excel (same exact rows, just pasted from SQL).  Any idea why that would happen?

 

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    Hi Flynnk31,

     

    As you rank values in No_ groups, please use the matrix instead of table visual to display rank values. You can place No_column in Row group of matrix. And have you tried the Rank Measure suggested by Sean?

     

    Rank Measure = RANKX( ALLEXCEPT('Table', 'Table'[No_]), [Total Cost Measure] ,, DESC)

     

     

    Best Regards,
    Qiuyun Yu