cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stemar_Aubert Regular Visitor
Regular Visitor

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

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.

 

2019-10-22 08-14-59_Window.png

 

Now however,  my solution creates the following issues:

 

2019-10-22 08-13-44_Window.png

 

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.

2019-10-22 08-27-16_Window.png

(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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

@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.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

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

@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.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)