Forum Discussion

sachintandon84's avatar
6 years ago
Solved

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

12 Replies

    • sachintandon84's avatar
      sachintandon84
      Helper 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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
    • sachintandon84's avatar
      sachintandon84
      Helper 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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        Update your PBI Desktop to the latest version.

        Best
        D
    • eseeef's avatar
      eseeef
      New Member

      Elegant solution for cumulative sum. Thanks for sharing this.