Forum Discussion
Request for help with RANKX function (DAX measure)
Hi everyone,
I am facing an issue with the RANKX DAX. Can anyone help me please?
The table in the screenshot below should display the quantity of each brand purchased. There are 153 brands that i need to rank and the RANKX function needs to rank the lowest as 1, assign any dublicate "Share of Quantity" values the same number and then skip to the next ranking.
Here are the DAX functions I used for the displayed columns:
Share of quantity = [Quantity purchased] / [Total quantity]
ROUND = ROUND(CALCULATE(([Quantity purchased] / [Total quantity]) * 100), 1)
RANK_FINAL = RANKX(ALL(APRIL_2024_PURCHASES[BRANDS]), [ROUND], [ROUND], ASC, Skip)
The ROUND function was an alternative attempt, as the "Share of quantity" function didn't seem to work.
As you can see in the screenshot below, you'll notice that the ranking jumps from "1" to "13" instead of continuing to 6. How is this possible? What do you think could be the error? I've looked so many times on Microsoft Learn and YouTube and still couldn't fix it... 😓 Could you please help? Thanks in advance!🙏
Try this measure using the RANK function. It's preferred over RANKX in most cases.
RANK_FINAL = VAR SourceTable = ADDCOLUMNS ( ALLSELECTED ( APRIL_2024_PURCHASES[BRANDS] ), "@ShareOfQuantity", [Share of quantity] ) VAR Result = RANK ( DENSE, SourceTable, ORDERBY ( [@ShareOfQuantity], ASC ) ) RETURN Resulthttps://www.sqlbi.com/articles/introducing-the-rank-window-function-in-dax/
3 Replies
Try this measure using the RANK function. It's preferred over RANKX in most cases.
RANK_FINAL = VAR SourceTable = ADDCOLUMNS ( ALLSELECTED ( APRIL_2024_PURCHASES[BRANDS] ), "@ShareOfQuantity", [Share of quantity] ) VAR Result = RANK ( DENSE, SourceTable, ORDERBY ( [@ShareOfQuantity], ASC ) ) RETURN Resulthttps://www.sqlbi.com/articles/introducing-the-rank-window-function-in-dax/
OMG! It works now. 😄 Thank you so much DataInsights for your help and time 🙏
