Return a constructed column to MAX()

[rhl94]

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

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. 

[az38]

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

[rhl94]

Unfortunately that does not seem to work:

The Rank column is the desired outcome.

[az38 · Accepted solution]

@rhl94 

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