Forum Discussion
RANKX: Convert measure to calculated column
After struggling with RANKX for several days, I got the following measure to correctly rank the master accounts by the sum of their respective revenues across all their sub-accounts (AccountKey):
Revenue Rank =
VAR _TABLE =
SUMMARIZE ( ALLSELECTED ( Account ), Account[MasterAccountKey], "Direct Revenue", [Direct Revenue] )
VAR _ADDRANK =
ADDCOLUMNS ( _TABLE, "Rank", RANKX ( _TABLE, [Direct Revenue],, DESC, DENSE ) )
RETURN
IF (
HASONEVALUE ( Account[MasterAccountKey] ),
SUMX ( FILTER ( _ADDRANK, Account[MasterAccountKey] = MAX ( Account[MasterAccountKey] ) ), [Rank] )
)
This approach loads incredibly slow due to the size of the data and I'd like to turn this into a calculated column to reduce load times for the users. However, using the code above gives me a circular dependency error when placed in a calculated column and the following results in all ranks equal 1:
RANKX(
ALLSELECTED(Account[MasterAccountKey]),
[Direct Revenue],
,
DESC,
Dense
)
Relationships (no control over this)
Account[AccountKey] 1:* Revenue[AccountKey] (Active)
Market[MarketKey] 1:* Revenue[MarketRevenueKey] (Inactive, but this is the relationship that I need to use)
Measure Used
[Direct Revenue] =
CALCULATE(
SUM( Revenue[Revenue] ),
USERELATIONSHIP( Revenue[RevenueKey], Market[RevenueKey] )
)
Any advice on how to execute this as a calculated column or speed up the rank measure significantly is much appreciated!
6 Replies
I repurposed another answer on here for RANKX to fit my solution, but admittedly didn't question it too much once I got it to work as expected (until now).
I tried replacing the return with several variations of the following, but keep getting a Multiple columns cannot be converted to a scalar value error:
IF ( HASONEVALUE ( Client[UltimateParentAccountKey] ), CALCULATE( SUM(_ADDRANK[Rank]), FILTER ( _ADDRANK, Client[UltimateParentAccountKey] = MAX ( Client[UltimateParentAccountKey])) ) )Is this what you're suggesting?
Actually after cleaning up the code, the error is Column 'Rank' cannot be found or may not be used in this expression, but I am still not able to get it to work with SUM.
parry2k I want to create a calculated column in the table Account that ranks the MasterAccountKey by the measure [Direct Revenue] which sums the revenue in the Revenue table.
The Account table has many MasterAccountKeys.
The above measure provides the expected result, but is very slow due to the size of the dataset, so I want to convert the logic in the measure into a calculated column.
