Top N Locations based on 2 measures

Hi Anonymous 

Just offering my take on it.

From your description, you want to rank Locations based on Score, but limited to Locations where sum of Total Loss Amount is nonblank - hopefully I interpreted that correctly 🙂

If Location will be included as a field in the visual, you can create a 0/1 measure for the purpose of filtering.

I have attached a test PBIX, with a Location Filter measure:

Location Filter = 
IF (
    NOT ISBLANK ( SUM ( 'Table'[Total Loss Amount] ) ),
    VAR LocationsForRanking = 
        FILTER (
            ALLSELECTED ( 'Table'[Location] ), -- You may want to use ALL rather than ALLSELECTED
            NOT ISBLANK ( CALCULATE ( SUM ( 'Table'[Total Loss Amount] ) ) )
        )
    VAR ScoreRank =
        RANKX ( 
            LocationsForRanking,
            CALCULATE ( SUM ( 'Table'[Score] ) )
        )
    RETURN
        INT ( ScoreRank >= 1 && ScoreRank <= [Top N RG Value] )
)

A visual-level filter is then applied to any visuals, with this measure set equal to 1.

The visuals themselves can then be sorted by Score.

This method works regardless of which measures are shown in the visual. 

Is this close to what you were wanting to do?

Regards,

Owen

Thanks a lot OwenAuger for your help, it is exactly what I wanted, However I have implemented the below code its working for me, please see which is better (performance wise also as using Direct Query)

var ranking2 = if(isblank(calculate(sum('Table'[Total Loss]))), blank(),
rankx(filter(All('Table'[Location name]), not(isblank(calculate(sum('Table'[Total Loss]))))),
calculate(sum('Table'[Total Loss])),, desc,skip))
VAR _tab2 =
CALCULATETABLE (
VALUES ( 'Table'[Location Name] ),
FILTER ( 'Table', not(isblank(sum('Table'[Total Loss]))) )
)

Return

SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Location Name]
IN _tab1 && ranking1 <= RankingSelect
)
,
sum(Score)
)