Forum Discussion
Solved
Return a constructed column to MAX()
Hi
I'm looking to create a ranking as a measure.
It looks like the following:
RANKX(ALL(Corporate),CALCULATE(SUM(Corporate[Amount])),,ASC,Dense)
I'd like, in the measure, to divide the ranking with the highest rank, so basically;
DIVIDE (
RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE ),
MAX ( RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE ))
)
RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE ),
MAX ( RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE ))
)
Issue is, MAX() expect a column and I'm returning a value. The goal is to create a percentile ranking, so I can see the if the corporate is in the e.g. top 1%. I need this to be a measure, as it has to be recalculated based on different context.
what if try to use ALL()?
DIVIDE ( RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE ), MAXX (ALL ( Corporate ), RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE )) )do not hesitate to give a kudo to useful posts and mark solutions as solution
3 Replies
Hi rhl94
try MAXX function https://docs.microsoft.com/en-us/dax/maxx-function-dax , smth like
DIVIDE ( RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE ), MAXX (Corporate, RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE )) )do not hesitate to give a kudo to useful posts and mark solutions as solution
Unfortunately that does not seem to work:
The Rank column is the desired outcome.
what if try to use ALL()?
DIVIDE ( RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE ), MAXX (ALL ( Corporate ), RANKX ( ALL ( Corporate ), CALCULATE ( SUM ( Corporate[Amount] ) ), , ASC, DENSE )) )do not hesitate to give a kudo to useful posts and mark solutions as solution
