Forum Discussion

DataSkills's avatar
DataSkills
Icon for Resolver I rankResolver I
1 year ago
Solved

Rankx should be easy! Why doesn't this work?

I am attempting to create the simplest of Rank measures! I have a single table (SalesData) of data, with a column called "Cost of Sales". 

I want to create a basic rankx function which ranks a dimension by the Cost of Sales. Sounds simple! But I cannot get this to show a result other than 1 every time no matter how I write this DAX.  Any suggestions? I am sure it's something daft!

 

 

COS Rank =
//Attempt 1
//rankx(SalesTable, sum(SalesTable[Cost of sales]))

//Attempt 2
//rankx(all(SalesTable), sum(SalesTable[Cost of sales]))

//Attempt 3
//rankx(all(SalesTable), CALCULATE(sum(SalesTable[Cost of sales])))

//Attempt 4
//rankx(SalesTable, CALCULATE(sum(SalesTable[Cost of sales])))

//Attempt 5
//rankx(SalesTable, [COS Measure])

//Attempt 6
//rankx(all(SalesTable), [COS Measure])

//Attempt 7
//rankx(all(SalesTable), [COS Measure])

//Attempt 8
rankx(SalesTable, CALCULATE([COS Measure]))

 

You can download a sample here. 

 

Just click on the download link at top right to download the PBIX. 

 

Thanks!

DAX
RANKX
  • ValtteriN's avatar
    ValtteriN
    1 year ago

    Hello, 

    You were on the right track by using ALL. The only issue now is that since you remove all filters the values are same for all rows so every row returns one. By adding a column reference  the calculation should work. E.g. all(SalesTable[Region])

    I recommend checking this article by SQLBI: Introducing RANKX in DAX - SQLBI

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/