topic Re: Can't break RANK TIES (cannot use RAND()) in DAX Commands and Tips

Can't break RANK TIES (cannot use RAND())

sachintandon84 — Fri, 17 Apr 2020 12:14:33 GMT

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] )
)
)
)

Re: Can't break RANK TIES (cannot use RAND())

Anonymous — Sat, 18 Apr 2020 18:22:26 GMT

Here's how to rank correctly on a non-time dimension. Please adjust it to your needs. No randomness necessary.

File attached.

Best

Re: Can't break RANK TIES (cannot use RAND())

sachintandon84 — Sat, 18 Apr 2020 20:06:40 GMT

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

Re: Can't break RANK TIES (cannot use RAND())

Anonymous — Sun, 19 Apr 2020 11:35:52 GMT

I'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

Re: Can't break RANK TIES (cannot use RAND())

Mariusz — Sun, 19 Apr 2020 14:48:58 GMT

Hi sachintandon84

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

Re: Can't break RANK TIES (cannot use RAND())

sachintandon84 — Sun, 19 Apr 2020 20:28:44 GMT

ok thanks. I'll wait till I get access to my pc.

Reza Rad's solution makes use of a date column, by dividing that by a large value and adding that to the column being ranked.

Not a super neat or logical solution, so will wait till I can get access to read your DAX.

thanks again,

Sachin

Re: Can't break RANK TIES (cannot use RAND())

sachintandon84 — Sun, 03 May 2020 15:40:04 GMT

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

Re: Can't break RANK TIES (cannot use RAND())

Anonymous — Sun, 03 May 2020 15:49:40 GMT

Update your PBI Desktop to the latest version.

Best
D

Re: Can't break RANK TIES (cannot use RAND())

sachintandon84 — Mon, 04 May 2020 17:54:42 GMT

Hi,

I tried updating it yesterday, but it kept saying I had the latest version (from 2017), and kept giving the 'object instance error' when I tried to open your pbix file.

The funny thing is I can upload the pbix file to power Bi online (through the get data option). I can even edit the report online. But online, it won't allow me to view the DAX. And when I try opening the file on desktop, I get that error saying the file can not be opened : (

Re: Can't break RANK TIES (cannot use RAND())

Anonymous — Mon, 04 May 2020 18:35:54 GMT

I've just downloaded the file and it opens without the slightest problem. Maybe you have to reinstall your PBI Desktop? All I can do is tell you the version of PBI Desktop I use and re-attach the file.

Release:
March 2020

Product Version:
2.79.5768.1082 (20.03) (x64)

Best

Re: Can't break RANK TIES (cannot use RAND())

sachintandon84 — Fri, 08 May 2020 09:22:37 GMT

Thanks!

My version was 2 months old. It now works.

This is a prety neat solution!

I see that you don't even use the RANKX function, but instead a nested double FILTER function - which I've not seen before. But it works.

The Cumulative Total function is also quite different, to what I had envisioned, but it works.

Thanks again for this!

Sachin

Re: Can't break RANK TIES (cannot use RAND())

eseeef — Wed, 15 Jun 2022 19:22:23 GMT

Elegant solution for cumulative sum. Thanks for sharing this.

Re: Can't break RANK TIES (cannot use RAND())

sunshine1 — Fri, 18 Nov 2022 19:23:35 GMT

you are the man. This was the only solution that worked!