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