Forum Discussion
Need help with a Rank function in DAX
Hey guys, i need some help with a rank column in pbi. the situation is this:
I need a column that ranks the last column (price) within each group of rows (defined by countrytier for rank global).
in the example above, need to result as the first row would have 1, the second row 1, the third row 2, fourth 2, fifth 3...
Hope you all get the meaning.
Hi julianst24
Since one countrytier may have duplicated prices, we need to distinguish them when ranking. You can first add an Index column with Power Query Editor. Apply the change to the model.
Then create a calculated column with below code. It calculates the ranking based on the expression 'Table'[checking final price] + 'Table'[Index] * 0.000001.
Rank = RANKX ( FILTER ( 'Table', 'Table'[countrytier for rank global] = EARLIER ( 'Table'[countrytier for rank global] ) ), 'Table'[checking final price] + 'Table'[Index] * 0.000001, , ASC, DENSE )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Hi julianst24
Since one countrytier may have duplicated prices, we need to distinguish them when ranking. You can first add an Index column with Power Query Editor. Apply the change to the model.
Then create a calculated column with below code. It calculates the ranking based on the expression 'Table'[checking final price] + 'Table'[Index] * 0.000001.
Rank = RANKX ( FILTER ( 'Table', 'Table'[countrytier for rank global] = EARLIER ( 'Table'[countrytier for rank global] ) ), 'Table'[checking final price] + 'Table'[Index] * 0.000001, , ASC, DENSE )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Thanks!!
