cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
danno Member
Member

RANKX using Column Issue: It Ties Values which shouldn't be tied

Hi, I am following posts to understand RANKX and am working on the Column based solution https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

I have created a version of the measure to work with Articles within Departments (Each Dept has unique set of Articles).  Each Article has a separate table with Sales figures, joined using an Article_No to the Article Dimension.  

The Measure calculates a ranking within each department, to identify top selling articles within each Dept: 

Article Rank by Dept Column = RANKX( FILTER('dim_article', 'dim_article'[Dept]=EARLIER('dim_article'[Dept]))

[Net Sales])  and Net Sales is a measure in a separate table - simply a sum over the sales column.  

The issue is the result - this is filtered using a slicer to a single dept, so the list should rank using the Net Sales.  

1 ACCEPTED SOLUTION

Accepted Solutions
Mariusz New Contributor
New Contributor

Re: RANKX using Column Issue: It Ties Values which shouldn't be tied

Hi @danno 

Try the below

Article Rank by Dept Column = 
RANKX( 
    CALCULATETABLE(
        dim_article, 
        ALLEXCEPT( dim_article, dim_article[Dept] ) 
    ),
    [Net Sales],
    ,,DENSE 
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


3 REPLIES 3
danno Member
Member

Re: RANKX using Column Issue: It Ties Values which shouldn't be tied

Article Rank by Dept ColumnNet Sales £k
1158992.6
3148215.3
3146118.4
4136670.7
6126321.9
6125466.7
8113794.9
9113731.1
9111070.4
11110030
Mariusz New Contributor
New Contributor

Re: RANKX using Column Issue: It Ties Values which shouldn't be tied

Hi @danno 

Try the below

Article Rank by Dept Column = 
RANKX( 
    CALCULATETABLE(
        dim_article, 
        ALLEXCEPT( dim_article, dim_article[Dept] ) 
    ),
    [Net Sales],
    ,,DENSE 
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


danno Member
Member

Re: RANKX using Column Issue: It Ties Values which shouldn't be tied

Thanks Marius, that worked a treat!!