How would i derive an ordinal rank - meaning 1,2,3,4,…so on, irrespective of duplicate values.
Rank Test.xlsx (8.8 KB)
If possible in the future, can you detail your question a bit more inside the forum post and add a demo Power BI model.
Images inside the post can be helpful also.
This is how you can get the fastest assistance from those on the forum.
Check out the below for more information on this.
https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30
Appreciate it.
Sam
For the ranking you mention.
Here’s a summary below
Skip Rank = RANKX( ALL( 'Values' ), [Total Value], , ASC, Skip )
Dense Rank = RANKX( ALL( 'Values' ), [Total Value], , ASC, Dense )
Are you sure you even have your example correct?
This just seems like it’s counting 1 -7 ?
Wouldn’t C be 2?
Maybe just clear this up before I dive any deeper into it.
Thanks
Sam
Hi - my bad, the ranking should be based on values, so…1,3,2,4,5,6,7.
Attached the PBIX, there is not much in it besides the sample data. The Rank column in pbix is what I am looking to obtain via dax. I already tried variations of rankx and could only get to dense and skip ranking numbers, which are not what I am looking for.
Thank you.
Rank Unique.pbix (28.9 KB)
Did research on this issue and found when there are ties, PBI has no way of breaking those. But is it possible to rank on Alphabet(A-Z) if there are ‘value’ ties?
This one is tougher than I thought initially. I’ve been working on a solution for a while but haven’t landed on one that works in every situation yet.
Will be back once I can get something that works.
One thing I believe you need is an index number. I don’t think you can work this out without one.
This is where I’m currently at.
So close but not exactly correct.
Sam
Ok I actually just worked it out.
It’s not super simple, but it get’s the right answer with this example data.
I’ll add the file so you can have a look through.
Let me know if you see any issues with it.
The ultimate formula could possible be integrated into one function. Maybe have a look at that.
I’ve just spent a significant amount of time on this one so have to move on.
Attached
Rank Unique.pbix (34.8 KB)
Chrs
Sam
Thank you for the time and efforts. I appreciate. Will look into the solution pbix.
Apologize for not being thorough enough on the initial post. Life’s been tough and stressful on all fronts past few weeks.
I did initially think of an Index column, will this still work if my data is from OLTP systems where there are multiple rows against company A, B…
Will need to find a way to summarize the table to return single rows against each company, is that correct?
I’m not sure. I would have to see an extended example to see. I made it work with this example given I believe.