Nested Ranking based off 2 value

[Anonymous]

Hi all,

I am trying to create a visual level filter to show the top 5 in one region then top 5 in another region then sort the result by the value. 

For example I have table visaul that looks like this:

Customer	Region	Sales
A	INT	10
B	INT	20
C	INT	30
D	INT	40
E	INT	50
F	INT	60
G	INT	70
A	AMER	80
B	AMER	90
C	AMER	100
D	AMER	110
E	AMER	120
F	AMER	130
G	AMER	140
A	AMER	150
B	AMER	160

The desrired outcome would look like this:
I initally did the ranking in PQ but I face issues when adding filters on the page for the user

Customer	Region	Sales
B	AMER	160
A	AMER	150
G	AMER	140
F	AMER	130
E	AMER	120
G	INT	70
F	INT	60
E	INT	50
D	INT	40
C	INT	30

Thank you!

[CNENFRNL]

Simple enough,

[TomMartens · Accepted solution]

Hey @Anonymous ,

next to the approach from the linked thread this measure usually performs better:

Measure 2 = 
var currentRegion = CALCULATE( FIRSTNONBLANK( 'Table (2)'[Region] , 'Table (2)'[Region] ) )
var currentSales = CALCULATE( SUM( 'Table (2)'[Sales] ) )
var rankCountrows = 
COUNTROWS(
    FILTER(
        ALLSELECTED( 'Table (2)' )
        ,  'Table (2)'[Region] = currentRegion && 'Table (2)'[Sales] >= currentSales
    )
)
return
IF(
    rankCountrows <= 5
    , rankCountrows
    , BLANK()
)

It allows to create this table visual:

Be aware that the table visual is sorted by two columns (Sort by more than one column in Power BI - YouTube)

1. region
2. Measure 2

Hopefully, this provides what you are looking for

Regards,

Tom

[Anonymous]

@TomMartens perfect, thank you for the help!!!!!

[TomMartens]

Hey @Anonymous ,

does this provide what you are looking for: Solved: Ranking within a group - Microsoft Power BI Community

Regards,

Tom