"Ranking with a Twist" Rankx based on a Measure

[Rbfull]

Hello,

I have spent a good deal of time browsing for a solution, and have decided to create my first post after not finding a similar (enough) situation.

Background:

We are rolling out a new process/software at various locations and want to capture adaptation rates and proficiencies for each location as the "go live".

The model is extremely simple: one Fact Table, one Calendar Table and one Dimension Table.

I'm trying to generate a rank for each location based on a measure value. Since more/new/different locations will be added and removed over time only the locations (Divisions) that have data in the latest month should be considered for this ranking.
I have done that by using the following measures:

This is the base Metric:

Aging - Complete to Released to Bill =
AVERAGE ( Compliance[Complete to Released to Bill] )

This returns the above for the latest month with data:

Complete to Release (This Month) =
CALCULATE (
    [Aging - Complete to Released to Bill],
    FILTER ( 'Calendar', 'Calendar'[MonthYear] = [Max Month] )
)

This returns the latest month with transaction info: [Load count] is just a basic distinctcount()

Max Month =
CALCULATE (
    MAX ( 'Calendar'[MonthYear] ),
    FILTER ( ALL ( 'Calendar' ), [Load Count] )
)

Finally - the Measure to rank Divisions that have data:

CTR = 
     Calculate(
         IF(
    NOT(ISBLANK([Complete to Release (This Month)])),
    RANKX(
        Filter(All('Department Hierarchy'[Division]), Not( Isblank([Complete to Release (This Month)]))),
        [Complete to Release (This Month)]
    ,,ASC,Skip)),ALL('Department Hierarchy'[OperationDivision])
)

This works beautifully on the surface returning the following results.

The issue arises (only sometimes) when a single Division is selected- Compare these views to above:

It's curious to me that the calculation seems to work fine if multiple divisions are selected, but doesn't always work when only one is selected. This is an issue since the design of the report will be for an executive or regional management to slice to a single region/division/location and quickly see how they rank vs others.

Ideally the ranking could be calculated up and down the hierarcy- but that is beyond my abilities at this time.

I'm fairly new to working with virtual tables and PBI in general... I feel like I'm pretty close but am obviously missing something, just not sure what it is.

In case its needed for testing - the slicer is using the hierarchy below

GetHelp.xlsx

Thanks in advance!!

[v-stephen-msft]

Hi @Rbfull ,

Try to replace the ALL function with ALLSELECTED function.

The ALLSELECTED function removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.