Run RANKX on partial data, and keep ranking in filtered tables visuals.

[Stemar_Aubert]

Hello everyone,

I have a question regarding RANKX(). My report consists of a leaderboard ranking different Districts based on a calculated measure (YoY Growth in %). The tricky thing is that I must "place" each District under its respective Region. You have North America, South America, etc. Also, the top three Districts have their own box at the top.

Now however,  my solution creates the following issues:

On this table, you see the results of my RANKX() formula, which doesn't work.

Districts with "OTHER" in their names are filtered out from the ranking (but must be kept in the data model as their values add up to Regional level), and I think they are the cause of the problem.

Currently, my RANKX() formula is this one :

Rank  = if(hasonevalue(Local[DISTRICT]),

calculate(rankx(FILTER(all(Local),Local[Custom]=0),FPins[PINS YTD YoY Δ]),

ALLEXCEPT(Local,Local[DISTRICT])),

"-")

Local is a table with District, Region and Global Region Data.

The Local[Custom]=0 is a way for me to flag "OTHER" districts (they have the value 1 so I can exclude them).

FPins in the big table with historical data, and PINS YTD YoY Δ is a measure of YTD Growth.

I use the if statement in order to display "-" when, on another page, somebody selects more than one District.

Currently, the model looks like so.

(The 3P part is an oddity that I must keep to calculate an aggregate number, but it has no link whatsoever in the Ranking. At the moment, the relations are inactive, but they are set to active when the model is running. I disabled them for testing at the moment.)

So in summary, my question is, what variation of RANKX() (or maybe EARLIER()) can I use in order to run a ranking on a sample of my Districts (exclusion of OTHERS), but also having the RANKX() stay valid when I filter out the Districts by Leader Boxes and Regional Boxes.

Thanks a lot for your help and for reading up to here.

[v-yuta-msft · Accepted solution]

@Stemar_Aubert ,

You may modify your measure using dax below and check if it can meet your requirement:

Rank =
IF (
    HASONEVALUE ( Local[DISTRICT] ),
    RANKX (
        FILTER (
            Local,
            Local[Custom] = 0
                && Local[DDISTRICT] = EARLIER ( Local[DISTRICT] )
        ),
        FPins[PINS YTD YoY Δ]
    ),
    "-"
)

Community Support Team _ Jimmy Tao

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