Forum Discussion
Can't break RANK TIES (cannot use RAND())
Hi,
I need to do a cumulative running total on a non-date measure, and have been able to do this, by creating a dynamic rank measure column (see below), and then another TOPN Measure that uses this Rank Measure. The Rank is done on a person's total year amount (why you see the calculate function remove the date filter by doing ALL ([Calender_Date])
The problem is that there are a few RANK TIES, that causes the cumulative measure to stay flat, and then jump.
I tried adding RAND ( ), as suggested in many other places, but it causes the measure to return very strange results. (the RANK for each person varies from month to month, when it should be the same across all months (since I'm ranking on the yearly amount), and sometimes the RANK starts with a 2)
Is there a more robust and logical way of doing this, so that if there is a tied rank it looks at the column [Full Name] and then ranks alphabetically in DEC, or ASC order, on that?
I'm struggling!
Thanks in advance!
Rank Measure Created (used within a TOP N):
Rank Amount by Business Unit:=IF (
ISFILTERED ( UK_Journals_All_Months[Full Name] ),
RANKX (
GROUPBY (
ALLEXCEPT (
UK_Journals_All_Months,
UK_Journals_All_Months[Department - P&L],
Nominal_Ledger[Nominal Code Description]
),
[Full Name]
),
CALCULATE (
SUM ( UK_Journals_All_Months[Amount] ),
ALL ( Calendar_Table[Date] )
),
,
DESC,
SKIP
),
COUNTROWS (
GROUPBY (
ALLEXCEPT (
UK_Journals_All_Months,
UK_Journals_All_Months[Department - P&L],
Calendar_Table[Date] ,
Nominal_Ledger[Nominal Code Description]
),
[Full Name]
)
)
)
TOP N Measure that returns cumulative amount:
Amount Cumulative of Business Unit:=CALCULATE (
CALCULATE (
SUM ( UK_Journals_All_Months[Amount] )),
TOPN (
[Rank Amount by Business Unit],
GROUPBY (
ALLEXCEPT (
UK_Journals_All_Months,
UK_Journals_All_Months[Department - P&L],
Nominal_Ledger[Nominal Code Description]
),
[Full Name]
),
CALCULATE (
SUM ( UK_Journals_All_Months[Amount] ),
ALL ( Calendar_Table[Date] )
)
)
)
- Anonymous6 years ago
Here's how to rank correctly on a non-time dimension. Please adjust it to your needs. No randomness necessary.
File attached.
Best
D
12 Replies
- AnonymousNot applicable
Here's how to rank correctly on a non-time dimension. Please adjust it to your needs. No randomness necessary.
File attached.
Best
D
- sachintandon84Helper I
Thanks. I'm on a Mac right now, and can't see the DAX in the pbix file.
Are you able to post the DAX.
Thanks in advance.
Sachin
- AnonymousNot applicableI'm sorry, Sachin, I can't do it as there is more than one measure required and I don't have time to copy everything and explain how it works. It's all in the file.
Best
D
- sachintandon84Helper I
Thanks.
I keep getting the following error when trying to open the pbix file:
Object reference not set to an instance of an object
Are you able to post a Excel Power Pivot solution?
Sachin
- AnonymousNot applicableUpdate your PBI Desktop to the latest version.
Best
D
- eseeefNew Member
Elegant solution for cumulative sum. Thanks for sharing this.
- MariuszCommunity Champion
Please read this article by Reza Rad, there is a section on breaking ties towards the end.
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn