Ranking with multiple filters

[Anonymous]

Hello, 

I'm having difficultly creating a card which displays the ranking for a company against other companies in it's region or if they change the filter to their ranking against companies in all regions. Ideally the output would be as follows:

You are ranked [ X ] out of [ Y ]

Where [ X ] is their ranking and [ Y ] depends on their region (default view) or regions selected. 

e.g.

1. Company A logs on to the report so a page filter is applied 

2. Company A belongs to a region with other companies 

2. Company A should see their ranking in their region (but shouldn't see the information related to other companies)

e.g. You are ranked 2 out of 3 (for their region)

3. If they change the filter on Region to ALL they should be able to see their ranking across all regions

e.g. You are ranked 6th out of 8

Any hints/tips would be appreciated.

Thanks,

Helen

[Anonymous]

@Anonymous Please create following calculated column and check if it works

AllRegion = 
VAR _count = COUNT('Table'[Company])
VAR _rank = RANKX('Table','Table'[Sales],,DESC,Dense)
RETURN CONCATENATE("You are ranked ",CONCATENATE(_rank,CONCATENATE(" Out of ",_count)))

WithinRegion = 
VAR _name = 'Table'[Region]
VAR _count = CALCULATE(COUNT('Table'[Region]),ALLEXCEPT('Table','Table'[Region]))
VAR _rank = RANKX(FILTER('Table','Table'[Region]=_name),'Table'[Sales],,DESC,Dense)
RETURN CONCATENATE("You are ranked ",CONCATENATE(_rank,CONCATENATE(" Out of ",_count)))

 

[Anonymous]

Hi @Anonymous, Thank-you for taking the time with this. I'll set it up and let you know how it goes.  Helen

[Anonymous]

Hi @vimal_parmar, 

Thanks for your solution. It seems to work in the following scenario:

1. Your rank in 1 region (IRE)

2. Your rank in 'All regions' (IRE, FRA, GER )

However the additional scenario is 

3. Your rank against multiple filtered regions e.g.  (IRE and FRA) or (IRE, IND, GER, US, UK) or any combination.

At the moment I found a solution where I can dynamically get the ranking using measures but it doesn't output on a card. It seems to lose context an return 1.  Do you know if you can only do ranking using tables and calculated columns ? 

Just for your info. below is what the current solution looks like. It seems to work but it would be better to output on a card.

Thanks again for your solution and help.

Helen

1. Your rank in 1 region (IRE)

2. Your rank in 'All regions' (IRE, FRA, GER )

3. Your rank against multiple filtered regions e.g.  (IRE and FRA) or (IRE, IND, GER, US, UK) or any combination.

Tables:

Measures: